Step 3: Calculate Hours worked
In Cell F2, type: =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)
In cell G2, type =IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)
Step 4: Drag down and total
In cell F10, sum regular hours, =SUM(F2:F8)
In cell G10, sum overtime hours =SUM(G2:G8)
Step 4: Calculate Pay:
In cell F11, type: =SUM(F10*$J$1)
In cell F12, type =SUM(G10*$J$2)
Sum F11 and F12 for total pay. Type =SUM(F11+G11)
Date
Log in
Lunch Starts
Lunch Ends
Log Out
Regular Hours
Overtime Hours
Monday
9:00 AM
1:00 PM
1:30 PM
6:00 PM
8.00
0.50
Tuesday
9:20 AM
11:57 AM
12:15 PM
6:00 PM
8.00
0.37
Wednesday
9:00 AM
12:00 PM
12:32 PM
5:15 PM
7.72
0.00
Thursday
0.00
0.00
Friday
0.00
0.00
Saturday
0.00
0.00
Sunday
0.00
0.00
Total Hours
23.72
0.87
Pay
$260.88
$14.30
Total Pay
$275.18
Bookmarks