Calculate hour total for each week

CaptainGravyBum

Board Regular
Joined
Dec 1, 2023
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I'm after some help please.
I need to calculate our driver weekly hours for payroll from the sheet below. I have the daily hours and the day they were for, but I need to find a way to total the hours worked for each week - ideally Sunday to Saturday, but it may only be weekdays that show for some drivers. I figure there's a COUNTIF formula required somewhere, but as the data in the sheet isn't per week, it's ongoing information, i'm not sure how to just total the weeks as they go along.
Book3
ABCDEF
1DateDriver NameArrival TimeEnd TimeHours Worked
216/12/2024Driver 106:0015:0509:05Mon
316/12/2024Driver 206:3015:3009:00Mon
417/12/2024Driver 206:3016:0009:30Tue
517/12/2024Driver 108:0016:3008:30Tue
618/12/2024Driver 105:0015:1010:10Wed
718/12/2024Driver 205:0014:0009:00Wed
819/12/2024Driver 106:3019:3013:00Thu
920/12/2024Driver 205:0015:0010:00Fri
1020/12/2024Driver 107:3017:4510:15Fri
1123/12/2024Driver 206:0015:1509:15Mon
1224/12/2024Driver 100:0000:0000:00Tue
1324/12/2024Driver 207:0012:0005:00Tue
1402/01/2025Driver 1SICKSICK
1503/01/2025Driver 205:0013:3008:30Fri
1606/01/2025Driver 200:0000:0000:00Mon
1706/01/2025Driver 1sicksick
1807/01/2025Driver 206:3018:1511:45Tue
1908/01/2025Driver 106:0017:4011:40Wed
2008/01/2025Driver 206:3017:2510:55Wed
2109/01/2025Driver 206:3000:0017:30Thu
2209/01/2025Driver 107:3017:0009:30Thu
2310/01/2025Driver 206:3000:0017:30Fri
2410/01/2025Driver 106:3000:0017:30Fri
2513/01/2025Driver 205:0015:0010:00Mon
2613/01/2025Driver 106:0018:3512:35Mon
2714/01/2025Driver 206:3016:3010:00Tue
2814/01/2025Driver 107:0017:3010:30Tue
2915/01/2025Driver 205:0013:4508:45Wed
3015/01/2025Driver 105:3015:3010:00Wed
3116/01/2025Driver 106:3016:1509:45Thu
3216/01/2025Driver 206:3015:3009:00Thu
3317/01/2025Driver 105:0016:2011:20Fri
3417/01/2025Driver 206:3016:0009:30Fri
3520/01/2025Driver 205:0015:0010:00Mon
3620/01/2025Driver 107:0016:4509:45Mon
3721/01/2025Driver 207:0014:4507:45Tue
3821/01/2025Driver 107:3016:2008:50Tue
3922/01/2025Driver 106:3018:3012:00Wed
4023/01/2025Driver 106:3017:3511:05Thu
4127/01/2025Driver 105:5518:5012:55Mon
4227/01/2025Driver 2OFF - CPC ISSUEOFF - CPC ISSUE
4328/01/2025Driver 106:3016:3010:00Tue
4429/01/2025Driver 106:3018:0011:30Wed
4530/01/2025Driver 106:3016:1009:40Thu
4631/01/2025Driver 205:0015:4510:45Fri
4731/01/2025Driver 106:3000:0017:30Fri
4803/02/2025Driver 106:1000:0017:50Mon
4903/02/2025Driver 206:3000:0017:30Mon
Sheet1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
There might be a problem with driver 1 on 12/24

MrExcelPlayground23.xlsx
ABCDEFGHIJ
1DateDriver NameArrival TimeEnd TimeHours WorkedDriver 1Driver 2
212/16/2024Driver 16:00:00 AM3:05:00 PM9:05MonSunday, December 15, 202451:0037:30
312/16/2024Driver 26:30:00 AM3:30:00 PM9:00MonSunday, December 22, 20240:0014:15
412/17/2024Driver 26:30:00 AM4:00:00 PM9:30TueSunday, December 29, 20240:008:30
512/17/2024Driver 18:00:00 AM4:30:00 PM8:30TueSunday, January 5, 202538:4057:40
612/18/2024Driver 15:00:00 AM3:10:00 PM10:10WedSunday, January 12, 202554:1047:15
712/18/2024Driver 25:00:00 AM2:00:00 PM9:00WedSunday, January 19, 202541:4017:45
812/19/2024Driver 16:30:00 AM7:30:00 PM13:00ThuSunday, January 26, 202561:3510:45
912/20/2024Driver 25:00:00 AM3:00:00 PM10:00FriSunday, February 2, 202517:5017:30
1012/20/2024Driver 17:30:00 AM5:45:00 PM10:15Fri
1112/23/2024Driver 26:00:00 AM3:15:00 PM9:15Mon
1212/24/2024Driver 112:00:00 AM12:00:00 AM0:00Tue
1312/24/2024Driver 27:00:00 AM12:00:00 PM5:00Tue
141/2/2025Driver 1SICKSICK
151/3/2025Driver 25:00:00 AM1:30:00 PM8:30Fri
161/6/2025Driver 212:00:00 AM12:00:00 AM0:00Mon
171/6/2025Driver 1sicksick
181/7/2025Driver 26:30:00 AM6:15:00 PM11:45Tue
191/8/2025Driver 16:00:00 AM5:40:00 PM11:40Wed
201/8/2025Driver 26:30:00 AM5:25:00 PM10:55Wed
211/9/2025Driver 26:30:00 AM12:00:00 AM17:30Thu
221/9/2025Driver 17:30:00 AM5:00:00 PM9:30Thu
231/10/2025Driver 26:30:00 AM12:00:00 AM17:30Fri
241/10/2025Driver 16:30:00 AM12:00:00 AM17:30Fri
251/13/2025Driver 25:00:00 AM3:00:00 PM10:00Mon
261/13/2025Driver 16:00:00 AM6:35:00 PM12:35Mon
271/14/2025Driver 26:30:00 AM4:30:00 PM10:00Tue
281/14/2025Driver 17:00:00 AM5:30:00 PM10:30Tue
291/15/2025Driver 25:00:00 AM1:45:00 PM8:45Wed
301/15/2025Driver 15:30:00 AM3:30:00 PM10:00Wed
311/16/2025Driver 16:30:00 AM4:15:00 PM9:45Thu
321/16/2025Driver 26:30:00 AM3:30:00 PM9:00Thu
331/17/2025Driver 15:00:00 AM4:20:00 PM11:20Fri
341/17/2025Driver 26:30:00 AM4:00:00 PM9:30Fri
351/20/2025Driver 25:00:00 AM3:00:00 PM10:00Mon
361/20/2025Driver 17:00:00 AM4:45:00 PM9:45Mon
371/21/2025Driver 27:00:00 AM2:45:00 PM7:45Tue
381/21/2025Driver 17:30:00 AM4:20:00 PM8:50Tue
391/22/2025Driver 16:30:00 AM6:30:00 PM12:00Wed
401/23/2025Driver 16:30:00 AM5:35:00 PM11:05Thu
411/27/2025Driver 15:55:00 AM6:50:00 PM12:55Mon
421/27/2025Driver 2OFF - CPC ISSUEOFF - CPC ISSUE
431/28/2025Driver 16:30:00 AM4:30:00 PM10:00Tue
441/29/2025Driver 16:30:00 AM6:00:00 PM11:30Wed
451/30/2025Driver 16:30:00 AM4:10:00 PM9:40Thu
461/31/2025Driver 25:00:00 AM3:45:00 PM10:45Fri
471/31/2025Driver 16:30:00 AM12:00:00 AM17:30Fri
482/3/2025Driver 16:10:00 AM12:00:00 AM17:50Mon
492/3/2025Driver 26:30:00 AM12:00:00 AM17:30Mon
Sheet31
Cell Formulas
RangeFormula
I1:J1I1=TRANSPOSE(UNIQUE(B2:B49))
H2:H9H2=UNIQUE(INT((A2:A49-1)/7)*7+1)
I2:J9I2=MMULT(--(TOROW(INT((A2:A49-1)/7)*7+1)=H2#),--TRANSPOSE(((TOROW(B2:B49)=TRANSPOSE(I1#))*TRANSPOSE(E2:E49))))
Dynamic array formulas.
 
Upvote 0
Another option if you have the pivotby function
Fluff.xlsm
ABCDEFGHIJK
1DateDriver NameArrival TimeEnd TimeHours Worked
216/12/2024Driver 16:00:0015:05:009:05:00Mon Driver 1Driver 2Total
316/12/2024Driver 26:30:0015:30:009:00:00Mon16/12/202451:00:0037:30:0088:30:00
417/12/2024Driver 26:30:0016:00:009:30:00Tue23/12/20240:00:0014:15:0014:15:00
517/12/2024Driver 18:00:0016:30:008:30:00Tue30/12/20240:00:008:30:008:30:00
618/12/2024Driver 15:00:0015:10:0010:10:00Wed06/01/202538:40:0057:40:0096:20:00
718/12/2024Driver 25:00:0014:00:009:00:00Wed13/01/202554:10:0047:15:00101:25:00
819/12/2024Driver 16:30:0019:30:0013:00:00Thu20/01/202541:40:0017:45:0059:25:00
920/12/2024Driver 25:00:0015:00:0010:00:00Fri27/01/202561:35:0010:45:0072:20:00
1020/12/2024Driver 17:30:0017:45:0010:15:00Fri03/02/202517:50:0017:30:0035:20:00
1123/12/2024Driver 26:00:0015:15:009:15:00MonTotal264:55:00211:10:00476:05:00
1224/12/2024Driver 10:00:000:00:000:00:00Tue
1324/12/2024Driver 27:00:0012:00:005:00:00Tue
1402/01/2025Driver 1SICKSICK
1503/01/2025Driver 25:00:0013:30:008:30:00Fri
1606/01/2025Driver 20:00:000:00:000:00:00Mon
1706/01/2025Driver 1sicksick
1807/01/2025Driver 26:30:0018:15:0011:45:00Tue
1908/01/2025Driver 16:00:0017:40:0011:40:00Wed
2008/01/2025Driver 26:30:0017:25:0010:55:00Wed
2109/01/2025Driver 26:30:000:00:0017:30:00Thu
2209/01/2025Driver 17:30:0017:00:009:30:00Thu
2310/01/2025Driver 26:30:000:00:0017:30:00Fri
2410/01/2025Driver 16:30:000:00:0017:30:00Fri
2513/01/2025Driver 25:00:0015:00:0010:00:00Mon
2613/01/2025Driver 16:00:0018:35:0012:35:00Mon
2714/01/2025Driver 26:30:0016:30:0010:00:00Tue
2814/01/2025Driver 17:00:0017:30:0010:30:00Tue
2915/01/2025Driver 25:00:0013:45:008:45:00Wed
3015/01/2025Driver 15:30:0015:30:0010:00:00Wed
3116/01/2025Driver 16:30:0016:15:009:45:00Thu
3216/01/2025Driver 26:30:0015:30:009:00:00Thu
3317/01/2025Driver 15:00:0016:20:0011:20:00Fri
3417/01/2025Driver 26:30:0016:00:009:30:00Fri
3520/01/2025Driver 25:00:0015:00:0010:00:00Mon
3620/01/2025Driver 17:00:0016:45:009:45:00Mon
3721/01/2025Driver 27:00:0014:45:007:45:00Tue
3821/01/2025Driver 17:30:0016:20:008:50:00Tue
3922/01/2025Driver 16:30:0018:30:0012:00:00Wed
4023/01/2025Driver 16:30:0017:35:0011:05:00Thu
4127/01/2025Driver 15:55:0018:50:0012:55:00Mon
4227/01/2025Driver 2OFF - CPC ISSUEOFF - CPC ISSUE
4328/01/2025Driver 16:30:0016:30:0010:00:00Tue
4429/01/2025Driver 16:30:0018:00:0011:30:00Wed
4530/01/2025Driver 16:30:0016:10:009:40:00Thu
4631/01/2025Driver 25:00:0015:45:0010:45:00Fri
4731/01/2025Driver 16:30:000:00:0017:30:00Fri
4803/02/2025Driver 16:10:000:00:0017:50:00Mon
4903/02/2025Driver 26:30:000:00:0017:30:00Mon
Sheet6
Cell Formulas
RangeFormula
H2:K11H2=PIVOTBY(WORKDAY.INTL(A2:A49+1,-1,"0111111"),B2:B49,E2:E49,SUM)
Dynamic array formulas.
 
Upvote 0
Hi
James,
This appears to be spot on using the demo data, but when I apply it to my live data, I'm only getting the first total for each driver on the first day of the week specified.
Is there a limit to the amount of data these formulas can use?
 
Upvote 0
Hi @Fluff,
Thanks for the suggestion, it's a bit odd though because as with the other answer above, when I apply this to my data, it only gives me the total for the first day of the week and not the whole week total.
I'm not sure if there are data restrictions or something else causing the problem.
 
Upvote 0
Make sure that the values in col E are all numbers, rather than text. Also the output needs to be formatted as [h]:mm:ss
 
Upvote 0
In H1
Excel Formula:
=LET(a,A2:A49,dr,B2:B49,tm,E2:E49,Za,UNIQUE(INT((a-1)/7)*7+1),Zb,TRANSPOSE(UNIQUE(dr)),zc,MAKEARRAY(ROWS(Za),COLUMNS(Zb),LAMBDA(r,c,SUMPRODUCT(tm*(dr=INDEX(Zb,c))*(a>=INDEX(Za,r))*(a<=(INDEX(Za,r)+6))))),VSTACK(HSTACK("Start Days",Zb),HSTACK(Za,zc)))
Book1
HIJ
1Start DaysDriver 1Driver 2
2Sunday, December 15, 202451:00:0037:30:00
3Sunday, December 22, 202400:00:0014:15:00
4Sunday, December 29, 202400:00:0008:30:00
5Sunday, January 5, 202538:40:0057:40:00
6Sunday, January 12, 202554:10:0047:15:00
7Sunday, January 19, 202541:40:0017:45:00
8Sunday, January 26, 202561:35:0010:45:00
9Sunday, February 2, 202517:50:0017:30:00
Sheet3
 
Upvote 0
o_O now you're just showing off ;).

This works fine on the rows in my original example, but as soon as I change the ranges from row 49 to say 1000 to capture all my data, all I get are value errors.
Such a shame because any one of these examples should work for me, I just can't extend the data.
Any idea what could be going wrong?
 
Upvote 0
How about like
Excel Formula:
=PIVOTBY(WORKDAY.INTL(A2:A1000+1,-1,"0111111"),B2:B1000,E2:E1000,SUM,,,,,,A2:A1000<>"")
 
Upvote 0
How about like
Excel Formula:
=PIVOTBY(WORKDAY.INTL(A2:A1000+1,-1,"0111111"),B2:B1000,E2:E1000,SUM,,,,,,A2:A1000<>"")
I'm getting figures with that formula, but they aren't correct, I can't even get them to match for a single day. I have the hours works and output cells formatted as Time hh:mm:ss, if I convert it to number I end up with 0.33 instead of 8 hours. Does it cause an issue if there are any rows with missing data?
 
Upvote 0

Forum statistics

Threads
1,226,267
Messages
6,189,940
Members
453,583
Latest member
Ok_category1816

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