Use the WORKDAY function to calculate the number of working days between two dates
Another useful function is the WORKDAY function.
This function returns a day that is some number of workdays into the future or before some date. A “workday” is days excluding weekends and any holidays that are specified.
The function requires two arguments and has an optional third.
=WORKDAY(start_date, days, [holidays])
- start_date – this required argument is the date from when you want to count the number of workdays
- days – this required argument is the number of days from the start date you want a count of. Using a negative number will give you the date that many workdays before your start date.
- holidays – this argument is optional. This allows you to add holiday dates in the formula for the WORKDAY function to skip along with weekends.
First, note that we have listed 3 holiday dates in cells A8, A9, and A10. We will be using these in our examples for the ‘holiday’ argument to exclude along with weekends.
On row 2, we want to know the date that is 90 workdays from Monday, November 14, 2016.
Excluding the weekends and holidays (A8:A10), this will be Tuesday, March 21, 2017 according to the WORKDAY function.
On row 3, we can instantly see the effect of weekends and holidays on the outcome of our formula. We want the date that is 2 workdays from Wednesday, November 23, 2017.
Because of the holiday on Thursday, November 24 – and the weekend days Saturday and Sunday – the result is Monday, November 28, 2016.
The third example in the figure illustrates how to use WORKDAY to find a date before the start date.
We want to know the date that is 20 workdays before Monday, January 17, 2017.
So, our DAY argument needs to be a negative value (-20).
Again, note the result considers weekend days as well as a couple of holidays.