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: 40

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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: 15
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

HappyChappy
  • Question
Timesheet Very Basic needs an expert eye
Replies
1
Views
202
HappyChappy
HappyChappy
M
  • Question
Spreadsheet to calculate overtime and total hours
Replies
1
Views
685
HyraxWithAFlamethrower
H
Z
  • Question
Fix timesheet totals for regular time and overtime, Maybe Macro
2
Replies
18
Views
520
Bosquedeguate
Bosquedeguate
T
  • Solved
Time Sheet with Shift Differentials
2
Replies
16
Views
3K
Toadstool
Toadstool
R
  • Question
Calculating Man Hours Worked on Day Night Shift
Replies
0
Views
2K
richy89
R

Forum statistics

Threads
1,215,073
Messages
6,122,976
Members
449,095
Latest member
Mr Hughes

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