Calculate total days based on work days per week and hours worked per day

M

Mafu1267

New Member
Joined
Oct 29, 2020
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

I have a project which has say 400 man hours worth of work
I need to calculate the duration in calendar weeks for this project, based on hours worked by day & days worked per week
I also need to know if Saturday & Sundays are worked, how many in total there are

Total Job Hours
400​
Hours/DayHours/DayHours/Day
Mon-Fri10Mon-Fri10Mon-Fri10
Sat0Sat8Sat8
Sun0Sun0Sun4
Total Calendar Weeks8Total Calendar Weeks7Total Calendar Weeks6 + 3 daysRounded Up
Saturdays WorkedSaturdays Worked7Rounded UpSaturdays Worked6
Sundays WorkedSundays WorkedSundays Worked6

I'm by far not an expert user & am somewhat stumped

Hope this makes sense

Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
AB
1Total Job Hours400
2
3Hours/Day
4Mon-Fri10
5Sat4
6Sun4
7
8Total Calendar Weeks6.6
9
10Saturdays Worked7
11Sundays Worked6
Summary
Cell Formulas
RangeFormula
B8B8=ROUND(DOLLARFR(B1/(B4*5+B5+B6),7),1)
B10B10=IF(B5=0,"",INT(B8)+(MOD(B8,1)*10>=6))
B11B11=IF(B5=0,"",INT(B8)+(MOD(B8,1)*10>6))
 
Upvote 0
Solution
The results will be sensitive to the day of the week for the start date.
The plus 1 was necessary with these date to be over the 400 hours.

T202010b.xlsm
ABCDEF
1Total Job Hours400StartEnd
2Mon 31-Aug-20Fri 16-Oct-20
3Hours
4Mon-Fri10Mon - Fri35350
5Sat8Sat648
6Sun4Sun64
7402
8Total Weeks6.4516129
8a
Cell Formulas
RangeFormula
E2E2=D2+B8*7+1
F4F4=E4*10
F5F5=E5*8
E4E4=NETWORKDAYS.INTL(D2,E2,1)
E5E5=NETWORKDAYS.INTL(D2,E2,"1111101")
E6E6=NETWORKDAYS.INTL(D2,E2,"1111110")
F7F7=SUM(F4:F6)
B8B8=B1/(B4*5+B5+B6)
 
Upvote 0
Edit correction to one calculation.

T202010b.xlsm
ABCDEF
1Total Job Hours400StartEnd
2Mon 31-Aug-20Wed 14-Oct-20
3Hours
4Mon-Fri10Mon - Fri33330
5Sat8Sat648
6Sun4Sun624
7402
8Total Weeks6.4516129
8a
Cell Formulas
RangeFormula
E2E2=D2+B8*7-1
E4E4=NETWORKDAYS.INTL(D2,E2,1)
E5E5=NETWORKDAYS.INTL(D2,E2,"1111101")
E6E6=NETWORKDAYS.INTL(D2,E2,"1111110")
F4:F6F4=E4*B4
F7F7=SUM(F4:F6)
B8B8=B1/(B4*5+B5+B6)
 
Upvote 0
How accurate do you need your calculation to be, please?
Assuming US Federal Holidays and work to start at 8:00 on weekdays and at 9:00 on Saturdays and Sundays, your third example will result in different results depending on your start date:
MrExcel_Calculate total days based on work days per week and hours worked per day.xlsm
MNOP
20Date StartDate End= Hours= Weeks
2120-Nov-2020 08:0011-Jan-2021 12:00304:006,08
2202-Nov-2020 08:0019-Dez-2020 17:00320:006,4
2326-Nov-2020 08:0016-Jan-2021 15:00310:006,2
24
25Date StartDate End= Hours= Saturdays
2620-Nov-2020 08:0011-Jan-2021 12:0064:008
2702-Nov-2020 08:0019-Dez-2020 17:0056:007
2826-Nov-2020 08:0016-Jan-2021 15:0062:007,75
29
30Date StartDate End= Hours= Sundays
3120-Nov-2020 08:0011-Jan-2021 12:0032:008
3202-Nov-2020 08:0019-Dez-2020 17:0024:006
3326-Nov-2020 08:0016-Jan-2021 15:0028:007
MrExcel 30.10.2020 12.55am
 
Upvote 0
T202010b.xlsm
ABCE
1StartEnd
2Total Job Hours400Mon 9-Nov-20Wed 23-Dec-20
3HoursHours
4Mon-Fri10330
5Sat848
6Sun424
7Total hours402
8Total Weeks6.45Sat and Sun days12
9Saturdays worked6
10Sundays Worked6
11
8a



T202010b.xlsm
ABCDE
1StartEndEnd
2Total Job Hours400Mon 9-Nov-20Thu 24-Dec-20Wed 23-Dec-20
3HoursHoursHours
4Mon-Fri10340330
5Sat84848
6Sun42424
7Total hours412402
8Total Weeks6.45Sat and Sun days1212
8a
Cell Formulas
RangeFormula
D2D2=C2+B8*7
E2E2=IF(D7>B2+4,-1+D2,IF(D7<B2,1+D2,D2))
D4D4=NETWORKDAYS.INTL(C2,D2,1)*B4
E4E4=NETWORKDAYS.INTL(C2,E2,1)*B4
D5D5=NETWORKDAYS.INTL(C2,D2,"1111101")*B5
E5E5=NETWORKDAYS.INTL(C2,E2,"1111101")*B5
D6D6=NETWORKDAYS.INTL(C2,D2,"1111110")*B6
E6E6=NETWORKDAYS.INTL(C2,E2,"1111110")*B6
D7:E7D7=SUM(D4:D6)
D8D8=D5/B5+D6/4
E8E8=E5/B5+E6/B6
B8B8=B2/(B4*5+B5+B6)
 
Upvote 0
T202010b.xlsm
ABCDEF
1Total Job HoursStartEndEndEnd Work Mon - FriEnd Work Mon - Sat
2400Fri 6-Nov-20Sun 20-Dec-20Mon 21-Dec-20Thu 31-Dec-20Thu 24-Dec-20
3HoursHoursHours
4Mon-Fri10310320400350
5Sat8565656
6Sun42828
7Total hours394404400406
8Sat and Sun days141407
9Weeks6.5787
10Hide this column
8a
Cell Formulas
RangeFormula
C2C2=WORKDAY.INTL(B2,A2/((10*5+8+4)/7)-1,"0000000",0)
D2D2=IF(C7>A2+4,-1+C2,IF(C7<A2,1+C2,C2))
E2E2=WORKDAY.INTL(B2,A2/B4-1,1,0)
F2F2=WORKDAY.INTL(B2,A2/((10*5+8)/6)-1+1,11)
C4C4=NETWORKDAYS.INTL(B2,C2,1)*B4
D4D4=NETWORKDAYS.INTL(B2,D2,1)*B4
E4E4=NETWORKDAYS.INTL(B2,E2,1)*B4
C5C5=NETWORKDAYS.INTL(B2,C2,"1111101")*B5
D5D5=NETWORKDAYS.INTL(B2,D2,"1111101")*B5
C6C6=NETWORKDAYS.INTL(B2,C2,"1111110")*B6
D6D6=NETWORKDAYS.INTL(B2,D2,"1111110")*B6
F4F4=NETWORKDAYS.INTL(B2,F2,"0000011")*B4
F5F5=NETWORKDAYS.INTL(B2,F2,"1111101")*B5
C7:F7C7=SUM(C4:C6)
C8C8=C5/B5+C6/4
D8D8=D5/B5+D6/B6
F8F8=F5/8
D9:F9D9=(D2-$B$2+1)/7
 
Upvote 0
T202010b.xlsm
ABCDEF
1Total Job HoursStartEndEndEnd Work Mon - FriEnd Work Mon - Sat
2400Fri 6-Nov-20Sun 20-Dec-20Mon 21-Dec-20Thu 31-Dec-20Thu 24-Dec-20
3HoursHoursHours
4Mon-Fri10310320400350
5Sat8565656
6Sun42828
7Total hours394404400406
8Sat and Sun days141407
9Weeks6.5787
10Hide this column
11
8a
Cell Formulas
RangeFormula
C2C2=WORKDAY.INTL(B2,A2/((10*5+8+4)/7)-1,"0000000",0)
D2D2=IF(C7>A2+4,-1+C2,IF(C7<A2,1+C2,C2))
E2E2=WORKDAY.INTL(B2,A2/B4-1,1,0)
F2F2=WORKDAY.INTL(B2,A2/((10*5+8)/6)-1+1,11)
C4C4=NETWORKDAYS.INTL(B2,C2,1)*B4
D4D4=NETWORKDAYS.INTL(B2,D2,1)*B4
E4E4=NETWORKDAYS.INTL(B2,E2,1)*B4
C5C5=NETWORKDAYS.INTL(B2,C2,"1111101")*B5
D5D5=NETWORKDAYS.INTL(B2,D2,"1111101")*B5
C6C6=NETWORKDAYS.INTL(B2,C2,"1111110")*B6
D6D6=NETWORKDAYS.INTL(B2,D2,"1111110")*B6
F4F4=NETWORKDAYS.INTL(B2,F2,"0000011")*B4
F5F5=NETWORKDAYS.INTL(B2,F2,"1111101")*B5
C7:F7C7=SUM(C4:C6)
C8C8=C5/B5+C6/4
D8D8=D5/B5+D6/B6
F8F8=F5/8
D9:F9D9=(D2-$B$2+1)/7
 
Upvote 0
Thanks all for the replies
Fluff was the one I needed as I had non-specific dates that I was working with - just an estimate of job hours
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
937
felixstraube
F
J
  • Solved
VBA or Excel – Time sheet with RT / OT based on the day of the week.
Replies
5
Views
245
awoohaw
awoohaw
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
304
ColinPearsonEIT
C
S
  • Question
Scheduling running staff hours
Replies
1
Views
178
awoohaw
awoohaw
W
  • Solved
Formula help
Replies
5
Views
178
Fluff
Fluff

Forum statistics

Threads
1,214,650
Messages
6,120,734
Members
448,987
Latest member
marion_davis

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