Both professionals and hobbyists depend on Microsoft Excel for scheduling and resource planning, and if that’s the case for you too, then the WORKDAY and WORKDAY.INTL formulas are two essential tools to have in your belt of Excel tools.

Both of these functions are used to return a new date based on a positive or negative number of days in the work week. That is, excluding weekends and holidays. Between the two functions, there is one major difference between WORKDAY and WORKDAY.INTL that I’ll cover below alongside an in depth explanation and examples of how you can use these Microsoft Excel functions.

Microsoft Excel – WORKDAY

Let’s look at the signature/prototype for this function:

WORKDAY:

1. start_date – required – the start date from which an offset date will be calculated and returned
2. days – required – the number of days in the future (positive number) or past (negative number) on which to offset start_date
3. holidays – optional – an array of one or more holidays that are to be excluded and considered non-working days when calculating the future/past date

Saturday and Sunday are considered the weekends and non-working days which, like Holidays, are excluded when the return date is being calculated. What does that mean, “…excluded when the return date is being calculated”?

Let’s say “start_date” was a Friday, for example, 9/21/2018. If I were to execute:

=WORKDAY("9/21/2018",1)

Then the return date would be 9/24/2018 because the formula moved forward one working day where workdays automatically exclude Saturday and Sunday:

Holidays work the same way as Saturday and Sundays, in that, they are dates that the WORKDAY function excludes. In this next video, notice that I setup my holidays using a table; doing so ensures that the WORKDAY function will be automatically updated when new entries are added to the list of holidays.

I started with 9/21/2018 and added one workday. With the default behavior, 9/22/2018 and 9/23/2018 were automatically skipped because they are Saturday and Sunday respectively. So, one workday ahead of 9/21/2018 was 9/24/2018. Well, then I made 9/24/2018 a holiday, so it was skipped. Then 9/25/2018 was made a holiday, and once more with 9/26/2018. Each time I added a holiday to the Holiday Dates table, the WORKDAY function’s holidays argument was automatically updated.

So how does WORKDAY.INTL differ?

Microsoft Excel – WORKDAY.INTL

If you look at the WORKDAY.INTL function signature/prototype, it’s very close to that of WORKDAY except it has two optional arguments: Weekends and Holidays:

As noted above, WORKDAY.INTL behaves the same as WORKDAY except for one major difference, and that’s the optional “[weekend]” argument. By default, both of these functions treat Saturday and Sunday as weekends (i.e. non-working days). For the WORKDAY.INTL function though, the unique “weekend’ argument gives you the option to overwrite that default behavior by using a string value to specify the days in the week that are weekends. That string must be exactly seven digits long – one digit for each day of the week.

• Each digit in the string represents a day in the order of Monday, Tuesday, Wednesday,…,Sunday.
• Each digit can be only the value 0 or 1. A 0 means the day is NOT considered a weekend day, and a 1 means the day is considered a weekend day (i.e. should be excluded).
• Note, the value 1111111 is invalid lest you have an infinite loop

So, the default (i.e. Saturday and Sunday) as weekend days would be represented: 0000011. If the project was based on a four day work week, with Friday also being a weekend day, that’d be 0000111.

What was demonstrated there:

• First the default behavior where adding one work day to a Friday pushed the new date out to Monday.
• Then, Monday was made a weekend day as well (i.e. 1000011), so one work day after a Friday became Tuesday.
• Once more, Tuesday was made a weekend day, so the next work day after a Friday was Wednesday (one can hope, right?)

Summary

Microsoft Excel makes it easy for a Project Manager, Business Analyst, Planner, or hobbyist to calculate an end date for a project with the WORKDAY and WORKDAY.INTL functions. Not only do these functions automatically include Saturday and Sunday as weekdays, but they let you dynamically specify holidays, and the WORKDAY.INTL function even lets you specify a custom work week with non-standard weekends.

Categories: Microsoft Excel, Office 365 and O365

Tags: Microsoft Excel, Office, Office 365 and O365