Automated Timecard Calculations

W

wheath

Board Regular
Joined
Jun 17, 2016
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I have a timecard I have created that I am having some trouble with 1 particular formula. Our state law on OT is over 40, not over 8 and our employer allows us to work more one day to make up hours another day. So I can't use some of the formulas I have seen out there. What I need it to do is calculate OT on days where over 8 hours were worked, but only if over 40 hours were worked that entire week.

This is the formula I have now, but it calculates the same OT hours over and over. So if I worked 1 hr OT over 40 hours, it is putting that on every day and giving me 5 hours OT total.

=IF(((C14-B14)+(E14-D14))+((C15-B15)+(E15-D15))+((C16-B16)+(E16-D16))+((C17-B17)+(E17-D17))+((C18-B18)+(E18-D18))*24>40,0,(((C14-B14)+(E14-D14))+((C15-B15)+(E15-D15))+((C16-B16)+(E16-D16))+((C17-B17)+(E17-D17))+((C18-B18)+(E18-D18)))*24-40)

Here is a snapshot of the spreadsheet. The above formula is found in the 5 cells below "OT Hours."


Excel 2010
ABCDEFGHI
1Wolgast Corporation
2
34835 Towne Centre
4Suite 203
5Saginaw, MI 48604
6Ph: 989-790-9120 Fx: 989-790-9053
7
8Employee Name:Wendy HeathTitle:Project Manager Assistant
9Employee Number:1781Status:Full-Time Hourly
10Department:Project ManagementSupervisor:Stephen R. Seibert
11
12
13DateStart TimeTime OutTime InEnd TimeRegular HoursOT HoursTotal Hours
1407/18/168:00 AM12:45 PM1:45 PM5:00 PM8.000.508.50
1507/19/168:00 AM12:30 PM1:00 PM5:00 PM8.500.509.00
1607/20/168:00 AM12:00 PM1:00 PM5:00 PM8.000.508.50
1707/21/168:00 AM12:00 PM1:00 PM5:00 PM8.000.508.50
1807/22/168:00 AM12:00 PM1:00 PM5:00 PM8.000.508.50
19WEEKLY TOTALS40.502.5043.00
20
21
22
23Comments:
24
25
26
27
28Employee Signature:Date:07/22/16
29
30Supervisor Signature:Date:
Sheet1
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Excel 2010
ABCDEFGHI
1Wolgast Corporation
2
34835 Towne Centre
4Suite 203
5Saginaw, MI 48604
6Ph: 989-790-9120 Fx: 989-790-9053
7
8Employee Name:Wendy HeathTitle:Project Manager Assistant
9Employee Number:1781Status:Full-Time Hourly
10Department:Project ManagementSupervisor:Stephen R. Seibert
11
12
13DateStart TimeTime OutTime InEnd TimeRegular HoursOT HoursTotal Hours
1407/18/168:00 AM12:45 PM1:45 PM5:00 PM8.00=if($F$19>40,$F13-8,0)
1507/19/168:00 AM12:30 PM1:00 PM5:00 PM8.50.5
1607/20/168:00 AM12:00 PM1:00 PM5:00 PM8.000
1707/21/168:00 AM12:00 PM1:00 PM5:00 PM8.000
1807/22/168:00 AM12:00 PM1:00 PM5:00 PM8.000
19WEEKLY TOTALS=Sum(G$14:G$18)=Sum(G$14:G$18)
20
21
22
23Comments:
24
25
26
27
28Employee Signature:Date:07/22/16
29
30Supervisor Signature:Date:

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​
 
Upvote 0
ghrain22 said:
Excel 2010
ABCDEFGHI
1Wolgast Corporation
2
34835 Towne Centre
4Suite 203
5Saginaw, MI 48604
6Ph: 989-790-9120 Fx: 989-790-9053
7
8Employee Name:Wendy HeathTitle:Project Manager Assistant
9Employee Number:1781Status:Full-Time Hourly
10Department:Project ManagementSupervisor:Stephen R. Seibert
11
12
13DateStart TimeTime OutTime InEnd TimeRegular HoursOT HoursTotal Hours
1407/18/168:00 AM12:45 PM1:45 PM5:00 PM8.00=if($F$19>40,$F13-8,0)
1507/19/168:00 AM12:30 PM1:00 PM5:00 PM8.50.5
1607/20/168:00 AM12:00 PM1:00 PM5:00 PM8.000
1707/21/168:00 AM12:00 PM1:00 PM5:00 PM8.000
1807/22/168:00 AM12:00 PM1:00 PM5:00 PM8.000
19WEEKLY TOTALS=Sum(G$14:G$18)=Sum(G$14:G$18)
20
21
22
23Comments:
24
25
26
27
28Employee Signature:Date:07/22/16
29
30Supervisor Signature:Date:

<tbody>
</tbody>
Click to expand...

That worked great! Now I see another issue ... in column F "Regular Hours" I need a formula that calculates the hours, but subtracts any OT if the hours for the week are over 40. See row 15 is double counting a half hour.

I've tried this formula in column F, but it isnt working:
=IF(F19>40,(((C14-B14)+(E14-D14)*24)-G14,((C14-B14)+(E14-D14))*24)
 
Last edited:
Upvote 0
Ah...I see your problem now.

ABCDEFGHI
1Wolgast Corporation
2
34835 Towne Centre
4Suite 203
5Saginaw, MI 48604
6Ph: 989-790-9120 Fx: 989-790-9053
7
8Employee Name:Wendy HeathTitle:Project Manager Assistant
9Employee Number:1781Status:Full-Time Hourly
10Department:Project ManagementSupervisor:Stephen R. Seibert
11
12
13DateStart TimeTime OutTime InEnd TimeRegular HoursOT HoursTotal Hours
1407/18/168:00 AM12:45 PM1:45 PM5:00 PM=if($H$19>40,min(8,$H14),$H14)=if($H$19>40,$H14-$F14,0)=(C14-B14+E14-D14)*24
1507/19/168:00 AM12:30 PM1:00 PM5:00 PM8.58.5
1607/20/168:00 AM12:00 PM1:00 PM5:00 PM808
1707/21/168:00 AM12:00 PM1:00 PM5:00 PM808
1807/22/168:00 AM12:00 PM1:00 PM5:00 PM808
19WEEKLY TOTALS=Sum(F$14:F$18)=Sum(G$14:G$18)=Sum(H$14:H$18)
20
21
22
23Comments:
24
25
26
27
28Employee Signature:Date:07/22/16
29
30Supervisor Signature:Date:

<tbody>
</tbody>

​
 
Last edited:
Upvote 0
ghrain22 said:
Ah...I see your problem now.

ABCDEFGHI
1Wolgast Corporation
2
34835 Towne Centre
4Suite 203
5Saginaw, MI 48604
6Ph: 989-790-9120 Fx: 989-790-9053
7
8Employee Name:Wendy HeathTitle:Project Manager Assistant
9Employee Number:1781Status:Full-Time Hourly
10Department:Project ManagementSupervisor:Stephen R. Seibert
11
12
13DateStart TimeTime OutTime InEnd TimeRegular HoursOT HoursTotal Hours
1407/18/168:00 AM12:45 PM1:45 PM5:00 PM=if($H$19>40,min(8,$H14),$H14)=if($H$19>40,$H14-$F14,0)=(C14-B14+E14-D14)*24
1507/19/168:00 AM12:30 PM1:00 PM5:00 PM8.58.5
1607/20/168:00 AM12:00 PM1:00 PM5:00 PM808
1707/21/168:00 AM12:00 PM1:00 PM5:00 PM808
1807/22/168:00 AM12:00 PM1:00 PM5:00 PM808
19WEEKLY TOTALS=Sum(F$14:F$18)=Sum(G$14:G$18)=Sum(H$14:H$18)
20
21
22
23Comments:
24
25
26
27
28Employee Signature:Date:07/22/16
29
30Supervisor Signature:Date:

<tbody>
</tbody>

​
Click to expand...

So, I implemented the changes above, working great! Until I worked a day with less than 8 hours. The timecard is not correctly totaling the RT and the OT. I tried changing the formula to take from the total in Column F, which it didnt like because it created a circular logic. Any suggestions?

https://drive.google.com/open?id=0BxlPk4r6K0G9Ulo5ZDdXN3lpN1k

I'd like to just do a snapshot using and html, but the Mr. Excel HTML quit working on my Excel 2010.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Time Sheet OT Calculations
Replies
1
Views
109
Speedyjrr
S
D
  • Question
Calculating OT Labor hours
Replies
5
Views
424
awoohaw
awoohaw
T
  • Question
Time Sheet question
2
Replies
15
Views
508
tfowlerusa
T
S
  • Solved
Formula shows ####### instead of 00:00 in cell
Replies
2
Views
253
Spicegold
S
esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
546
esset
esset

Forum statistics

Threads
1,214,821
Messages
6,121,762
Members
449,048
Latest member
excelknuckles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top