Calculating workdays between two dates when one cell is blank, or return no if zero

[SaraWitch]

Hello all.

I am trying to calculate the number of working days (and not counting Bank Holidays) between two cells (minus 1 day) and if the second is empty use today's date.

I can find the actual days with "=IF($X3="",TODAY(),$X3-$W3-1)", but this obviously counts all days and not just working days minus Band Holidays.

I would also like to return a blank cell result if no columns have dates.

Any help would be appreciated!

Ta muchly, folks!
Sara

 

[James006]

Hello,

Take a look at the Networkdays function ... :wink:

https://support.office.com/en-us/article/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7

HTH

 

[SaraWitch]

Thank you for your reply.

However, this will only calculate if dates are in both cells. If the second cell is empty, I want to use today's date. If both cells are empty, I want a blank return, rather than "43440" which is currently showing!

Ta muchly!

 

[SaraWitch]

Quick update: I have used "=IF($X3>0,$X3-$W3,TODAY()-$W3)", which is almost there, but does not calculate working days or return blank if both cells are empty...

 

[cdchapman]

Try the following formula. This assumes that bank holidays and other holiday dates are entered into cells AF2:AF26, so change this range to suit your holiday date list.

=IF(AND($W3="",$X3=""),"",IF(AND($W3<>"",$X3=""),NETWORKDAYS($W3,TODAY(),$AF$2:$AF$26),NETWORKDAYS($W3,X3,$AF$2:$AF$26)))

 

[SaraWitch]

Hello there.

Sorry for the delay - Christmas and all that!!

This formula is great; thank you. However, it is returning result of 1 when the days are the same so should be 0. I have tried minusing 1, but it doesn't seem to work...?

 

[cdchapman]

Try this amended formula (again it assumes that bank holidays and other holiday dates are entered into cells AF2:AF26):

=IF(AND($W3="",$X3=""),"",IF(AND($W3<>"",$X3=""),NETWORKDAYS($W3,TODAY(),$AF$2:$AF$26)-1,NETWORKDAYS($W3,X3,$AF$2:$AF$26)-1))

 

[SaraWitch]

Perfect - that's grand; thank you!