This video is part of our online video training for Excel formulas.
Calculate due dates with the WORKDAY function
In this video, we'll look at how to calculate due dates with the WORKDAY and WORKDAY.INTL functions.
The WORKDAY function returns a date in the future or past that takes into account weekends and optionally holidays.
You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days.
Now, to review, date calculations can be really simple.
To add 1 day to the date in B5, I can put 1 in C5, then just use the formula:
And this gives us December 23, as expected.
If I change C5 to -1, we get December 21.
This works because Excel stores all dates as serial numbers...
But what happens when you need to skip weekends or holidays? Well, that's what the WORKDAY function is for.
Let me first apply a custom number format to the dates so we can easily see the day of week.
Custom date format = mmm dd-mmm-yyy
The WORKDAY function takes 3 arguments: a start date, days, and an optional range for "holidays". To skip weekends, I just need to give WORKDAY the start date from column B, and the day value from column C.
With 1 day, the result is December 25, a Monday. WORKDAY skips the weekend.
If I change days to 2, we get December 26.
Now, December 25 is Christmas, so it wouldn't normally be treated as a workday.
To take holidays into account, we need to provide a range of dates that represent non-working days.
When I add that range as the 3rd argument, you can see WORKDAY exclude those dates from the calculation.
Finally, what if you need to treat a weekend day, like Saturday, as a workday?
In that case, you'll need to switch to the WORKDAY.INTL function, which allows you to customize weekends.
WORKDAY.INTL takes another optional argument called weekend, which goes in the 3rd position, followed by holidays.
For example, if Saturdays are normally workdays, you would supply the code 11 for weekend.
A more flexible way to customize weekends is to use a special mask based on a text string of 7 ones and zeros, where 1s represent non-working days in a week that begins on Monday.
So, if Saturdays are normally a working day, I'd use 6 zeros followed by 1.
This scheme lets you specify any combination of working and non-working days in a 7 day week.