Calc dates excluding weekends w/blanks

[greeneggsandsam]

I hav the following formula....
=IF(ISBLANK(F2),$U$1-D2,F2-D2)
Where U1 = TODAY()
D2 is the date submitted
F2 is the date returned

I want to be able to calculate the days between the dates where weekends and holidays are excluded (JUST BUSINESS DAYS)

[Jonmo1]

Try the NETWORKDAYS function

=NETWORKDAYS(D2,IF(ISBLANK(F2),$U$1,F2),H1:H20)

H1:H20 is a list of holiday dates you create that should be excluded.


NOTE, Networkdays is INCLUSIVE. Meaning it counts both the start AND end dates.
To account for this, either add 1 to the Start Date, or Subtract 1 from the End date, your choice.

To Exclude the start date
=NETWORKDAYS(D2+1,IF(ISBLANK(F2),$U$1,F2),H1:H20)

To Exclude the end date
=NETWORKDAYS(D2,IF(ISBLANK(F2),$U$1,F2)-1,H1:H20)

[greeneggsandsam]

Found a slight glitch with the formula.
If the start date and return date are the same, meaning the turn around was the same day then the formula reports a negative number when it should be either zero or one.

[Jonmo1]

That's a result of 'accounting for' the inclusive nature of networkdays.

Which do you want, 0 or 1 ?

We can use the MAX function for this
=MAX(0or1,NETWORKDAYS(....))