Calculating the Number of Weekday Hours between Two Dates in Excel

Question

I am trying to determine how much of the period between two instants in time falls on a workday (as determined by Excel's NETWORKDAYS function).  For example, the "weekday time" between Tuesday 11 PM and the following Wednesday 2 AM is 3 hours.  Ditto for Friday 11 PM through Monday 2 AM.  And so if the start time is Friday 8 PM and the end time is any time on Saturday or Sunday, the answer should be 4 hours — the portion of the interval that falls on the weekend (or holiday) should be ignored (or subtracted from the end-to-end duration).

The bottom portion of this question illustrates more examples.

I have found many things here but they deal with SQL, code, etc, and not a ton in Excel. I did find this, but it did not directly help me.

I am using this for the formula:

=NETWORKDAYS(F2,G2,Q$2:Q$7)-1-MOD(F2,1)+MOD(G2,1) 

and below is what is going on.

What am I missing here?

Answer 1

For starters, you're missing a clear problem statement.  What is the number of "weekday hours" between noon on Friday and noon on Saturday?

OK, I guess you are illustrating what you want in your bottom image.

To be less snarky, what you're missing (what you appear not to have noticed) is that, in all the examples where you say the result is wrong (and only in those instances), either your start date/time (Fn) or your end date/time (Gn) is a Saturday or a Sunday.  (Try using format ddd mm-dd-yyyy h:mm:ss instead of mm-dd-yyyy h:mm:ss.)  NETWORKDAYS(Wednesday,Friday) is 3.  But NETWORKDAYS(Wednesday,Saturday) and NETWORKDAYS(Wednesday,Sunday) are also 3.

Take your first example: Tue 07-03-2018 15:44:16 through Sun 07-08-2018 20:09:56.  NETWORKDAYS(Tuesday,Sunday) would normally be 4 (Tuesday, Wednesday, Thursday and Friday; ignoring the weekend), but NETWORKDAYS(Tuesday 07-03,Sunday 07-08) is 3 because of the 07-04 holiday.  So your formula works out to 3-1-(15:44:16)+(20:09:56), or 2+(20:09:56-15:44:16), or 2 days and 4:25:40, which equals 52:25:40, which is what Excel is giving you.  You didn't take info account the fact that

your_formula(Tue 07-03-2018 15:44:16, Sun 07-08-2018 20:09:56)

your_formula(Tue 07-03-2018 15:44:16, Fri 07-06-2018 20:09:56)

Fri06

Even more clear is the third row, where the start date/time is Sat 07-21-2018 6:30:12, and the discrepancy is 6:30:12.

---

I found a solution based on the fact that NETWORKDAYS(some_date, the_same_date, (holidays)) is 1 if the date is a work day and 0 if it is not.  So

=NETWORKDAYS(F2,G2,Q$2:Q$7)-NETWORKDAYS(G2,G2,Q$2:Q$7)-NETWORKDAYS(F2,F2,Q$2:Q$7)*MOD(F2,1)+NETWORKDAYS(G2,G2,Q$2:Q$7)*MOD(G2,1)

will give you the result you want.

Answer 2

You need to take into account that the start and end dates of the intervals may fall on a weekend (or holiday). In this case, the MOD terms should not be applied. Try this:

=(NETWORKDAYS(F2,G2,$Q$2:$Q$6)-IF(AND(WEEKDAY(F2,2)<6,ISNA(MATCH(INT(F2),$Q$2:$Q$6,0))),MOD(F2,1),0)+IF(AND(WEEKDAY(G2,2)<6,ISNA(MATCH(INT(G2),$Q$2:$Q$6,0))),MOD(G2,1)-1,0))

Hope that helps.

Answer 3

Your formula is highly dependent on controlling the input data. Currently, your formula will only work if the first and last day in the range of dates is a work day. This is due to MOD(F2,1)+MOD(G2,1) that takes the remainders of the first and last day in your range. If they happen to be a non-workday then your formula will be wrong.

Combining your formula with information found in an answer here you can make a formula do what you want from what you have.

Here:

=NETWORKDAYS(IF(WEEKDAY(F2)<5,F2,WORKDAY(F2,1,holidays)),IF(WEEKDAY(G2)<5,G2,WORKDAY(G2,-1,holidays)),holidays)-IF(WEEKDAY(F2,2)<6,MOD(F2,1),1)+IF(WEEKDAY(G2,2)<6,MOD(G2,1),0)

This checks the first date in the range for a workday, if it isn't then move to next. Then checks the end date for a workday, if not then move earlier. Then adjust the remainders if they happen to be workdays or not.

As in, it is exactly the same as your original formula, but with added checks on the dates themselves. The reference to "holidays" can be replaced with your cell references. I just used a named range for convenience.

Edit: WEEKDAY(F2)<5 may need to be replaced with WEEKDAY(F2,2)<6 to be sure you are working with Monday start workweeks.