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
 
You need to format the output times as [h]:mm:ss
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
You need to format the output times as [h]:mm:ss
Yep, that's got it, thanks.
Sorry for my ignorance, but what does the [h] signify other than hours? is it dynamic or something?

Also, the formula says WORKDAY, does that include weekends? ideally I need the totals to be Sunday to Saturday - bit weird but that's how we work it.
 
Upvote 0
The [h] is total hours.
Workday.intl is to get the week start dates, if it need to start on a Sunday then use
Excel Formula:
=PIVOTBY(WORKDAY.INTL(A2:A1000+1,-1,"1111110"),B2:B1000,E2:E1000,SUM,,,,,,A2:A1000<>"")
 
Upvote 0
Solution
The [h] is total hours.
Workday.intl is to get the week start dates, if it need to start on a Sunday then use
Excel Formula:
=PIVOTBY(WORKDAY.INTL(A2:A1000+1,-1,"1111110"),B2:B1000,E2:E1000,SUM,,,,,,A2:A1000<>"")
Excellent! Thanks for the explanation, I'd be lost without this forum. Really appreciate everyone's help :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
When I changed row number formula worked ok for me. Anyway I have modified the formula. Try.
Excel Formula:
=LET(Dt,FILTER(A2:E490,A2:A490<>""),a,INDEX(Dt,,1),dr,INDEX(Dt,,2),tm,INDEX(Dt,,5),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)))
 
Upvote 0
When I changed row number formula worked ok for me. Anyway I have modified the formula. Try.
Excel Formula:
=LET(Dt,FILTER(A2:E490,A2:A490<>""),a,INDEX(Dt,,1),dr,INDEX(Dt,,2),tm,INDEX(Dt,,5),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)))
Hi, I have tried again with this formula and it still just gives me the value error. Not sure what's wrong, but I have a workable solution with the PIVOTBY from @Fluff so I can use that.
Thanks again.
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,127
Members
453,641
Latest member
enfkkviesm

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