Distribute Hours Evenly Between Two Dates

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.


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.
                                                                                     
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:
   
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:
 
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))))

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).
 
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.


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