Distribute Hours Evenly Between Two Dates

Ryan Chong
CERTIFIED EXPERT
(NIV) Hebrews 10:35 So do not throw away your confidence; it will be richly rewarded.
Published:
Edited by: Andrew Leniart
Recently I have answered a question on how to distribute hours evenly between two dates. I thought this isn't that difficult to do that in Excel with formula, but what I see from this question, it could be applied to other similar scenarios. So, let's try and see what could we do here.
The original question is pretty straight forward with the statement below:

I need to distribute hours evenly between start and finish dates excluding Sunday, considering working days from Monday - Saturday (Saturday half day work). Hours to be distributed evenly from Monday-Saturday.
 
Below is what the asker provided.

Start DateFinish DateMHRs16-Dec-2017-Dec-2018-Dec-2019-Dec-2020-Dec-2021-Dec-2022-Dec-2023-Dec-2024-Dec-2025-Dec-2026-Dec-2027-Dec-2028-Dec-2029-Dec-2030-Dec-20
16/12/202030/12/20201600107107107107107107107107107107107107107107107

From the table above, the initial observations are:

1) The date range from the start date to finish date consists of 15 days (both mutually inclusive)
2) The total hours is 1600, if it's divided evenly between the start and finish dates, it would be 1600/ 15 = 106.67 (rounded to 107) per day.

So, now let's solve the original question in which we need to apply the following rules:

1) Exclude Sundays in the calculation. Meaning to say, when it's Sundays, there will be 0 hour on that day.
2) Half workday for Saturdays.



The steps

1) Calculate the number of working days between the start and finish dates

To do that, from the date range, we are using the formula: NETWORKDAYS.INTL

The formula could be like this:

=NETWORKDAYS.INTL($A$2,$B$2,11)


We could refer to the table below for the 3rd parameter to be used in NETWORKDAYS.INTL function.
                                                                                     
              Weekend number                          Weekend days            
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday 
4Tuesday, Wednesday 
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday 
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

We passed 11 as 3rd parameter to the formula to exclude the calculation for Sundays.

From the calculation above, you should get a result of 13. Meaning to say, there is 13 working days between 16 Dec to 30 Dec, consider that Sundays are off days.

2) Saturdays are half days

As per the requirement, the smaller unit of working is "half day", so for a full day, there will be 2 "half days".

3) Calculate how many Saturdays within the date range

To do that, we could use the formula:

=INT((WEEKDAY($A$2-7)-$A$2+$B$2)/7)

So, technically, if there are 2 Saturdays between the date range, we would need to minor off these 2 units of "half days" since it's not working.

4) Calculate total units of working

Since we have already gotten 13 working days, then it would be 13 x 2 = 26 working "half days". Finally, we need to minor off the 2 units of "half days" for Saturdays.

Hence, the total units of working between the date range is (13 x 2) - 2 = 24

5) Total man-hours and average per unit

Based on what is given, the total man-hours is 1600 hours. Then, we have total units of "half days", which is equal to 24.

To summarize:

Man hours per unit would be 1600 / 24 = 66.67 hours

And this could be calculated based on the formula below:

=$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11)*2)-INT((WEEKDAY($A$2 - 7)-$A$2+$B$2)/7))

6) Distribute the man-hours per unit to each day

Finally, we are checking the respective dates, and there are a couple of things to verify.

* If it's Saturday, it will take one man-hours per unit, which is 66.67 hours.
* If it's Sunday, it's off day, so the calculation will be zero.
* If it's other days, it will take two man-hours per unit, which is 66.67 hours x 2 = 133.33 hours.

To determine a weekday of date, we are using WeekDay function.

For example:

=WEEKDAY(D$1)

This function returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

We could use the formula below for a clearer illustration on working days:

=IF(WEEKDAY(D$1)=1,"Off", IF(WEEKDAY(D$1)=7,"Half", "Working"))

The result would be:
 
Start DateFinish DateMHRs16-Dec-2017-Dec-2018-Dec-2019-Dec-2020-Dec-2021-Dec-2022-Dec-2023-Dec-2024-Dec-2025-Dec-2026-Dec-2027-Dec-2028-Dec-2029-Dec-2030-Dec-20
16/12/202030/12/20201600107107107107107107107107107107107107107107107



WorkingWorkingWorkingHalfOffWorkingWorkingWorkingWorkingWorkingHalfOffWorkingWorkingWorking
 
To calculate for Saturdays, we simply re-use the formula at step 5 (since Saturdays got the smallest unit of working day):

=$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11)*2)-INT((WEEKDAY($A$2 - 7)-$A$2+$B$2)/7))

To calculate for Sundays, we need 2 units of "half days", so we use the formula above x 2:

=2*$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11)*2)-INT((WEEKDAY($A$2 - 7)-$A$2+$B$2)/7))

Finally, we need to put this formula into the conditions, and make the final formula like this:

=IF(WEEKDAY(D$1)=1,0, IF(WEEKDAY(D$1)=7,$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11)*2)-INT((WEEKDAY($A$2 - 7)-$A$2+$B$2)/7)), 2*$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11)*2)-INT((WEEKDAY($A$2 - 7)-$A$2+$B$2)/7))))

Once, this is done, we could test the results:
 
Start DateFinish DateMHRs16-Dec-2017-Dec-2018-Dec-2019-Dec-2020-Dec-2021-Dec-2022-Dec-2023-Dec-2024-Dec-2025-Dec-2026-Dec-2027-Dec-2028-Dec-2029-Dec-2030-Dec-20Total
16/12/202030/12/20201600107107107107107107107107107107107107107107107



WorkingWorkingWorkingHalfOffWorkingWorkingWorkingWorkingWorkingHalfOffWorkingWorkingWorking



133.33133.33133.3366.670.00133.33133.33133.33133.33133.3366.670.00133.33133.33133.331600.00

The result is verified to be correct. The total sum of the hours is equal to 1600 hours.



What's next?

The asker has actually asked further in the actual scenario:

How would be done by Year in one formula to destitute over the period of 2021-2022;
Lets say in 2021 we work as Mon-Saturday full day - Sunday off day and on 2022 we do Mon-Saturday full day - Sunday half day. 

Let's try if we could make this!

a) For 2021 model: Mon-Sat full day; Sundays off day

We could use this formula:

=IF(WEEKDAY(D$1)=1,0,$C$2/((NETWORKDAYS.INTL($A$2,$B$2,11))))

Start DateFinish DateMHRs16-Dec-2117-Dec-2118-Dec-2119-Dec-2120-Dec-2121-Dec-2122-Dec-2123-Dec-2124-Dec-2125-Dec-2126-Dec-2127-Dec-2128-Dec-2129-Dec-2130-Dec-21Total
16/12/202130/12/20211600107107107107107107107107107107107107107107107



WorkingWorkingWorkingOffWorkingWorkingWorkingWorkingWorkingWorkingOffWorkingWorkingWorkingWorking



123.08123.08123.080.00123.08123.08123.08123.08123.08123.080.00123.08123.08123.08123.081600.00

b) For 2022 model: Mon-Sat full day; Sundays off day

We could use this formula:

=IF(WEEKDAY(D$1)=1, $C$2/(((($B$2-$A$2+1)*2)-INT((WEEKDAY($A$2-1)-$A$2+$B$2)/7))), 2*$C$2/(((($B$2-$A$2+1)*2)-INT((WEEKDAY($A$2-1)-$A$2+$B$2)/7))))

Note: We will not use the formula: NETWORKDAYS.INTL in this case since all days are working days (weekdays and weekends).
 
Start DateFinish DateMHRs16-Dec-2217-Dec-2218-Dec-2219-Dec-2220-Dec-2221-Dec-2222-Dec-2223-Dec-2224-Dec-2225-Dec-2226-Dec-2227-Dec-2228-Dec-2229-Dec-2230-Dec-22Total
16/12/202230/12/20221600107107107107107107107107107107107107107107107



WorkingWorkingHalfWorkingWorkingWorkingWorkingWorkingWorkingHalfWorkingWorkingWorkingWorkingWorking



114.29114.2957.14114.29114.29114.29114.29114.29114.2957.14114.29114.29114.29114.29114.291600.00

c) Combine both 2021 and 2022 models together

We could use this formula:

=IF(IF(YEAR(D$1)=2021, IF(WEEKDAY(D$1)=1,"Off",  "Working"), IF(WEEKDAY(D$1)=1,"Half",  "Working"))="Half", $C$2/IF(YEAR($B$2)=2022, (NETWORKDAYS.INTL($A$2,DATE(2021,12,31),11)+$B$2-DATE(2022,1,1)+1)*2 -(INT((WEEKDAY(DATE(2022,1,1) -1)-DATE(2022,1,1)+$B$2)/7))), IF(IF(YEAR(D$1)=2021, IF(WEEKDAY(D$1)=1,"Off",  "Working"), IF(WEEKDAY(D$1)=1,"Half",  "Working"))="Off", 0, $C$2/IF(YEAR($B$2)=2022, (NETWORKDAYS.INTL($A$2,DATE(2021,12,31),11)+$B$2-DATE(2022,1,1)+1)*2 -(INT((WEEKDAY(DATE(2022,1,1) -1)-DATE(2022,1,1)+$B$2)/7)))*2))

Well, the formula getting more complex but it still produces the correct results.

Start DateFinish DateMHRs16-Dec-2117-Dec-2118-Dec-2119-Dec-2120-Dec-2121-Dec-2122-Dec-2123-Dec-2124-Dec-2125-Dec-2126-Dec-2127-Dec-2128-Dec-2129-Dec-2130-Dec-2131-Dec-211-Jan-222-Jan-223-Jan-224-Jan-225-Jan-226-Jan-227-Jan-228-Jan-229-Jan-2210-Jan-2211-Jan-2212-Jan-2213-Jan-2214-Jan-2215-Jan-2216-Jan-22Total
16/12/202116/1/202216005050505050505050505050505050505050505050505050505050505050505050



WorkingWorkingWorkingOffWorkingWorkingWorkingWorkingWorkingWorkingOffWorkingWorkingWorkingWorkingWorkingWorkingHalfWorkingWorkingWorkingWorkingWorkingWorkingHalfWorkingWorkingWorkingWorkingWorkingWorkingHalf



56.1456.1456.140.0056.1456.1456.1456.1456.1456.140.0056.1456.1456.1456.1456.1456.1428.0756.1456.1456.1456.1456.1456.1428.0756.1456.1456.1456.1456.1456.1428.071600.00

Note: This formula does not work if it's within the same year. And it doesn't really cover more complex scenarios such as cross to the 3rd year calculations. If such scenarios happened, it's always better to use some helper formula instead of applying a single complex formula for the ease of maintenance and troubleshooting.



Hope you enjoy this tutorial, attached the Excel file for your references.

Sample.xlsx
0
53 Views
Ryan Chong
CERTIFIED EXPERT
(NIV) Hebrews 10:35 So do not throw away your confidence; it will be richly rewarded.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community