0

I am seeking a formula that can calculate the number of weekdays between two specified dates, or if the end date is not provided, calculate the number of weekdays from the start date to the current date.

The formula I have tried so far is:

=IF(ISBLANK(C2),"",NETWORKDAYS(C2,TODAY()))

this works for 2 out of the 3 scenarios.
I have attached an image of the 3 examples.

First line is good. I don't need to know the days if there is a finish date.
Second line is good as it counts from the start date to today excluding weekends.

However, the third line is wrong, what I require is to stop counting to today and just count between start and finish dates, this needs to return 1 not 10.

=IF(ISBLANK(C5),"",NETWORKDAYS.INTL(C5,D5,1))

works for this line, but not for the other two.

Dates help

Improve this question

1 Answer 1

Reset to default
0

You need to handle the cases separately. Try this (where C2 is the start date if present and D2 is the end date if present):

=IF(ISBLANK(C2), "", IF(ISBLANK(D2), NETWORKDAYS(C2, TODAY()), NETWORKDAYS(C2, D2)))

This will handle the following three cases:

  • If there is no start date: return blank.
  • Otherwise, and if there is no end date: return workdays between the start date and today.
  • Otherwise (i.e. if both start and end dates exist): return workdays between the two.

You might need to supply a third argument to NETWORKDAYS() to suit your needs.

Improve this answer
1
  • 1
    Thank you for taking the time to respond. I have tried your formula and it works great.
    – PsychoPenguin
    Commented Jun 20 at 9:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .