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
 
Sort by date Sort by votes
Excel 2010
ABCDEFGHI
4835 Towne Centre
Suite 203
Saginaw, MI 48604
Ph: 989-790-9120 Fx: 989-790-9053
Employee Name:Title:
Employee Number:Status:
Department:Supervisor:
Comments:
Employee Signature:Date:
Supervisor Signature:Date:

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Wolgast Corporation[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Wendy Heath[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Project Manager Assistant[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: center"]1781[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Full-Time Hourly[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Project Management[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Stephen R. Seibert[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Regular Hours[/TD]
[TD="align: center"]OT Hours[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]07/18/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"] =if($F$19>40,$F13-8,0) [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]07/19/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"].5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]07/20/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]07/21/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]07/22/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]WEEKLY TOTALS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]07/22/16[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

</tbody>

​
 
Upvote 0
ghrain22 said:
Excel 2010
ABCDEFGHI
4835 Towne Centre
Suite 203
Saginaw, MI 48604
Ph: 989-790-9120 Fx: 989-790-9053
Employee Name:Title:
Employee Number:Status:
Department:Supervisor:
Comments:
Employee Signature:Date:
Supervisor Signature:Date:

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Wolgast Corporation[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Wendy Heath[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Project Manager Assistant[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: center"]1781[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Full-Time Hourly[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Project Management[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Stephen R. Seibert[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Regular Hours[/TD]
[TD="align: center"]OT Hours[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]07/18/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"] =if($F$19>40,$F13-8,0) [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]07/19/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"].5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]07/20/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]07/21/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]07/22/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]WEEKLY TOTALS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]07/22/16[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

</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
4835 Towne Centre
Suite 203
Saginaw, MI 48604
Ph: 989-790-9120 Fx: 989-790-9053
Employee Name:Title:
Employee Number:Status:
Department:Supervisor:
Comments:
Employee Signature:Date:
Supervisor Signature:Date:

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Wolgast Corporation[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Wendy Heath[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Project Manager Assistant[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: center"]1781[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Full-Time Hourly[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Project Management[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Stephen R. Seibert[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Regular Hours[/TD]
[TD="align: center"]OT Hours[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]07/18/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"] =if($H$19>40,min(8,$H14),$H14) [/TD]
[TD="align: right"] =if($H$19>40,$H14-$F14,0) [/TD]
[TD="align: right"] =(C14-B14+E14-D14)*24 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]07/19/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"].5[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]07/20/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]07/21/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]07/22/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]WEEKLY TOTALS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] =Sum(F$14:F$18) [/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"] =Sum(H $14:H$18) [/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]07/22/16[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

</tbody>

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

ABCDEFGHI
4835 Towne Centre
Suite 203
Saginaw, MI 48604
Ph: 989-790-9120 Fx: 989-790-9053
Employee Name:Title:
Employee Number:Status:
Department:Supervisor:
Comments:
Employee Signature:Date:
Supervisor Signature:Date:

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Wolgast Corporation[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Wendy Heath[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Project Manager Assistant[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: center"]1781[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Full-Time Hourly[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Project Management[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]Stephen R. Seibert[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Regular Hours[/TD]
[TD="align: center"]OT Hours[/TD]
[TD="align: center"]Total Hours[/TD]
[TD="align: center"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]07/18/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:45 PM[/TD]
[TD="align: right"]1:45 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"] =if($H$19>40,min(8,$H14),$H14) [/TD]
[TD="align: right"] =if($H$19>40,$H14-$F14,0) [/TD]
[TD="align: right"] =(C14-B14+E14-D14)*24 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]07/19/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"].5[/TD]
[TD="align: right"]8.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]07/20/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]07/21/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]07/22/16[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]WEEKLY TOTALS[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] =Sum(F$14:F$18) [/TD]
[TD="align: center"] =Sum(G$14:G$18) [/TD]
[TD="align: center"] =Sum(H $14:H$18) [/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"]07/22/16[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

</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

R
  • Solved
Holiday without adding Time in and Time Out
Replies
3
Views
51
Peter_SSs
Peter_SSs
V
  • Question Question
time and date fix
Replies
7
Views
186
vmjan02
V
V
  • Solved
vlookup not working in some case
Replies
4
Views
93
Peter_SSs
Peter_SSs
S
  • Question Question
Time Sheet OT Calculations
Replies
1
Views
431
Speedyjrr
S
V
  • Question Question
Adjust formula to calculate monthly dynamic average of work hours
Replies
2
Views
64
vladimiratanasiu
V

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