Timesheet Calculation

D

d3sti3

New Member
Joined
Nov 15, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I made this account specifically to ask this (probably very simple) question. I have gone through quite a few websites trying to get an answer but I can't seem to find one that works.

Standard time is Monday through Friday 7am to 6pm
Overtime is anything outside of that, so Monday through Friday 6pm to 7am the next day, as well as all hours of Saturday & Sunday.

The formula that I have seems to be able to calculate the standard time and overtime except for when the shift starts before midnight and the shift does not end until the next morning. In the picture, in the last row with a date (Nov 15, 2023) it has a start time of 11pm and an end time of 8am with a calculated amount of 9 OT hours. It should be 8 OT hours and 1 standard time hour, because 11pm to 7am would be OT and 7am to 8am would be standard time again.
When I changed the time to let's say 1am, it calculates it accurately, it's only when it passes through to the next day that my calculations are wrong.

Any info you can give is greatly appreciated, I've been working on this as a favor to a friend and I'm sure it's very simple, I just can't seem to see it. Let me know if you need any clarification.

FYI

The blue shows if anything is outside of standard time (M-F 7am-6pm); the green is any time on Saturday or Sunday.

Hours Calc:
IF(E9="","",IF(WEEKDAY(B9,2)>5,"",MAX((MIN(F9+(F9<E9),TIME(18,0,0))-MAX(E9,TIME(7,0,0)))*24,0)))
OT Calc:
IF(E9="","",IF(WEEKDAY(B9,2)>5,(F9-E9+(F9<E9))*24,ABS((F9+(F9<E9)-E9)*24)-MAX((MIN(F9+(F9<E9),TIME(18,0,0))-MAX(E9,TIME(7,0,0)))*24,0)))
 

Attachments

  • Capture.PNG
    Capture.PNG
    54.8 KB · Views: 64

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Hi,

See if this will help your friend along:
networkdays.intl.xlsx
ABCDEFGHIJKL
1Hours (Week day)OT Hours (excluding weekends)OT Hours Saturday / SundayDay Shift07:00:0018:00:00
2Datevendorw.o.#StartEnd0700-18001800-07000000-0000Sat/Sun00:00:0000:00:00
39-11-20234:00 PM8:00 PM2,002,00-
49-11-20234:00 PM1:00 AM2,007,00-
510-11-20236:00 AM4:00 PM9,001,00-
610-11-20236:00 PM3:00 AM-6,003,00
710-11-20238:00 PM3:00 AM-4,003,00
810-11-20239:00 AM2:00 PM5,00--
912-11-20239:00 AM5:00 PM--8,00
1013-11-20236:00 AM12:00 PM5,001,00-
1113-11-20237:00 AM6:00 PM11,000,00-
1214-11-20236:00 PM8:00 AM1,0013,00-
1315-11-20235:59 PM8:00 PM0,022,00-
1415-11-202311:00 PM8:00 AM1,008,00-
Sheet4
Cell Formulas
RangeFormula
L2L2=K2+1
F3:F14F3=IFERROR(((NETWORKDAYS.INTL((A3+D3),(A3+(E3<D3)+E3),"0000011",)-1)*($L$1-$K$1)+IF(NETWORKDAYS.INTL((IF(E3<D3,A3+1,A3)+E3),(IF(E3<D3,A3+1,A3)+E3),"0000011"),MEDIAN(MOD((IF(E3<D3,A3+1,A3)+E3),1),$L$1,$K$1),$L$1)-MEDIAN(NETWORKDAYS.INTL((A3+D3),(A3+D3),"0000011")*MOD((A3+D3),1),$L$1,$K$1))*24,0)
G3:G14G3=IF(WEEKDAY(A3,2)<6,(((A3+IF(AND(WEEKDAY(A3,2)=5,E3<D3),$L$2,E3))-(A3+D3)+((A3+IF(AND(WEEKDAY(A3,2)=5,E3<D3),$L$2,E3))<(A3+D3)))*24)-F3,0)
H3:H14H3=IFERROR(((NETWORKDAYS.INTL((A3+D3),(IF(E3<D3,A3+1,A3)+E3),"1111100")-1)*($L$2-$K$2)+IF(NETWORKDAYS.INTL((IF(E3<D3,A3+1,A3)+E3),(IF(E3<D3,A3+1,A3)+E3),"1111100"),MEDIAN(MOD((IF(E3<D3,A3+1,A3)+E3),1),$L$2,$K$2),$L$2)-MEDIAN(NETWORKDAYS.INTL((A3+D3),(A3+D3),"1111100")*MOD((A3+D3),1),$L$2,$K$2))*24,0)
 
Upvote 0
Thank you so much for your reply.

It seems like the formulas you provided are working in most instances except for in the boxes where I made the text red.
 

Attachments

  • Capture.PNG
    Capture.PNG
    39.9 KB · Views: 29
Upvote 0
Hi,

Just the remark "it seems like the formulas you provided are working in most instances except for in the boxes where I made the text red." isn't enough.
Are you able to use XL2BB to post a sample of your data instead a fixed screenshot and explain what's s wrong with the results of the formula because i can't replicate your results and without seeing what's in your sheet and how you have used my formula's, i can't help you.
 
Upvote 0
Apologies, I was having a hard time getting XL2BB to work for me.

Timesheet - Shift Differentials.xlsx
BCDEFGHIJ
6Sat/Sun OT0:00:000:00:00
7Day Shift7:00:0018:00:00
8datevendorw.o. #start timeend timehourso.t.weekend o.t.materials
99-Nov-20234:00 PM8:00 PM2.002.000.00
109-Nov-20234:00 PM1:00 AM2.007.000.00
1110-Nov-20236:00 AM4:00 PM9.001.000.00
1210-Nov-20236:00 PM3:00 AM0.000.000.00
1310-Nov-20238:00 PM3:00 AM0.0022.000.00
1410-Nov-20239:00 AM2:00 PM5.000.000.00
1512-Nov-20239:00 AM5:00 PM0.000.008.00
1613-Nov-20236:00 AM12:00 PM5.001.000.00
1713-Nov-20237:00 AM6:00 PM11.000.000.00
1814-Nov-20236:00 PM8:00 PM0.002.000.00
1915-Nov-20235:59 PM8:00 PM0.022.000.00
2015-Nov-202311:00 PM8:00 AM1.008.000.00
21   
22   
23   
24   
25   
26   
27Total35.0245.008.00
28Rate per hour$30.00$45.00$45.00
29Totals$1,050.50$2,025.00$360.00$0.00
30
31Total pay$3,435.50
Sheet1 (2)
Cell Formulas
RangeFormula
I6I6=H6+1
G9:G26G9=IF(E9="","",IFERROR(((NETWORKDAYS.INTL((B9+E9),(B9+(F9<E9)+F9),"0000011",)-1)*($I$7-$H$7)+IF(NETWORKDAYS.INTL((IF(F9<E9,B9+1,B9)+F9),(IF(F9<E9,B9+1,B9)+F9),"0000011"),MEDIAN(MOD((IF(F9<E9,B9+1,B9)+F9),1),$I$7,$H$7),$I$7)-MEDIAN(NETWORKDAYS.INTL((B9+E9),(B9+E9),"0000011")*MOD((B9+E9),1),$I$7,$H$7))*24,0))
H9:H26H9=IF(E9="","",IF(WEEKDAY(B9,2)<6,(((B9+IF(AND(WEEKDAY(B9,2)=5,F9<E9),$I$7,F9))-(B9+E9)+((B9+IF(AND(WEEKDAY(B9,2)=5,F9<E9),$I$7,F9))<(B9+E9)))*24)-G9,0))
I9:I26I9=IF(E9="","",IFERROR(((NETWORKDAYS.INTL((B9+E9),(IF(F9<E9,B9+1,B9)+F9),"1111100")-1)*($I$7-$H$7)+IF(NETWORKDAYS.INTL((IF(F9<E9,B9+1,B9)+F9),(IF(F9<E9,B9+1,B9)+F9),"1111100"),MEDIAN(MOD((IF(F9<E9,B9+1,B9)+F9),1),$I$7,$H$7),$I$7)-MEDIAN(NETWORKDAYS.INTL((B9+E9),(B9+E9),"1111100")*MOD((B9+E9),1),$I$7,$H$7))*24,0))
G27:I27G27=SUM(G9:G26)
G28G28=$I$5
H28:I28H28=$I$5*1.5
G29:I29G29=PRODUCT(G27,G28)
J29J29=SUM(J9:J26)
J31J31=SUM(G29,H29,I29,J29)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E9:E26Expression=IF(E9:E26="","",WEEKDAY(B9:B26,2)>5)textNO
F9:F26Expression=IF(E9:E26="","",WEEKDAY(B9:B26,2)>5)textNO
E9:F26Expression=IF(E9:F26="",,E9:F26>0.75)textNO
E9:F26Expression=IF(E9:F26="",,E9:F26<0.291666666666667)textNO
 
Upvote 0
Hi,

If you're changing formula's to fit in your sheet, make sure you check if you changed them correctly.
I checked your sheet and changed it so it reflected my original formula.

Book1
BCDEFGHIJ
6Sat/Sun OT00:00:0000:00:00
7Day Shift07:00:0018:00:00
8datevendorw.o. #start timeend timehourso.t.weekend o.t.materials
99-11-20234:00 PM8:00 PM2,002,00-
109-11-20234:00 PM1:00 AM2,007,00-
1110-11-20236:00 AM4:00 PM9,001,00-
1210-11-20236:00 PM3:00 AM-6,003,00
1310-11-20238:00 PM3:00 AM-4,003,00
1410-11-20239:00 AM2:00 PM5,00--
1512-11-20239:00 AM5:00 PM--8,00
1613-11-20236:00 AM12:00 PM5,001,00-
1713-11-20237:00 AM6:00 PM11,000,00-
1814-11-20236:00 PM8:00 PM-2,00-
1915-11-20235:59 PM8:00 PM0,022,00-
2015-11-202311:00 PM8:00 AM1,008,00-
21   
22   
23   
24   
25   
26   
27Total35,023314
28Rate per hour000
29Totals€ 0,00€ 0,00€ 0,00€ 0,00
30
31Total pay€ 0,00
32
Sheet1
Cell Formulas
RangeFormula
I6I6=H6+1
G9:G26G9=IF(E9="","",IFERROR(((NETWORKDAYS.INTL((B9+E9),(B9+(F9<E9)+F9),"0000011",)-1)*($I$7-$H$7)+IF(NETWORKDAYS.INTL((IF(F9<E9,B9+1,B9)+F9),(IF(F9<E9,B9+1,B9)+F9),"0000011"),MEDIAN(MOD((IF(F9<E9,B9+1,B9)+F9),1),$I$7,$H$7),$I$7)-MEDIAN(NETWORKDAYS.INTL((B9+E9),(B9+E9),"0000011")*MOD((B9+E9),1),$I$7,$H$7))*24,0))
H9:H20H9=IF(E9="","",IF(WEEKDAY(B9,2)<6,(((B9+IF(AND(WEEKDAY(B9,2)=5,F9<E9),$I$6,F9))-(B9+E9)+((B9+IF(AND(WEEKDAY(B9,2)=5,F9<E9),$I$6,F9))<(B9+E9)))*24)-G9,0))
I9:I20I9=IF(E9="","",IFERROR(((NETWORKDAYS.INTL((B9+E9),(IF(F9<E9,B9+1,B9)+F9),"1111100")-1)*($I$6-$H$6)+IF(NETWORKDAYS.INTL((IF(F9<E9,B9+1,B9)+F9),(IF(F9<E9,B9+1,B9)+F9),"1111100"),MEDIAN(MOD((IF(F9<E9,B9+1,B9)+F9),1),$I$6,$H$6),$I$6)-MEDIAN(NETWORKDAYS.INTL((B9+E9),(B9+E9),"1111100")*MOD((B9+E9),1),$I$6,$H$6))*24,0))
H21:H26H21=IF(E21="","",IF(WEEKDAY(B21,2)<6,(((B21+IF(AND(WEEKDAY(B21,2)=5,F21<E21),$I$7,F21))-(B21+E21)+((B21+IF(AND(WEEKDAY(B21,2)=5,F21<E21),$I$7,F21))<(B21+E21)))*24)-G21,0))
I21:I26I21=IF(E21="","",IFERROR(((NETWORKDAYS.INTL((B21+E21),(IF(F21<E21,B21+1,B21)+F21),"1111100")-1)*($I$7-$H$7)+IF(NETWORKDAYS.INTL((IF(F21<E21,B21+1,B21)+F21),(IF(F21<E21,B21+1,B21)+F21),"1111100"),MEDIAN(MOD((IF(F21<E21,B21+1,B21)+F21),1),$I$7,$H$7),$I$7)-MEDIAN(NETWORKDAYS.INTL((B21+E21),(B21+E21),"1111100")*MOD((B21+E21),1),$I$7,$H$7))*24,0))
G27:I27G27=SUM(G9:G26)
G28G28=$I$5
H28:I28H28=$I$5*1.5
G29:I29G29=PRODUCT(G27,G28)
J29J29=SUM(J9:J26)
J31J31=SUM(G29,H29,I29,J29)
 
Upvote 0
Solution
Thank you so very much. I really appreciate it.

Just one last question, I see the difference between the two ranges H9:H20 & H21:H26, if I drag down the formula from H9 down, it'll still calculate correctly?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Trying to use a Macro to fix timesheet numbers for regular time and overtime.
2
Replies
13
Views
482
myall_blues
myall_blues
HappyChappy
  • Question Question
Timesheet Very Basic needs an expert eye
Replies
1
Views
388
HappyChappy
HappyChappy
M
  • Question Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
1K
HyraxWithAFlamethrower
H
Z
  • Question Question
Fix timesheet totals for regular time and overtime, Maybe Macro
2
Replies
18
Views
873
Bosquedeguate
Bosquedeguate
T
  • Solved
Time Sheet with Shift Differentials
2
Replies
16
Views
5K
Toadstool
Toadstool

Forum statistics

Threads
1,224,994
Messages
6,182,241
Members
453,100
Latest member
RONCARE

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