How to Find the Workday a Specific Number of Days Before of After a “Start Date” in MS Excel by using WORKDAY.INTL Function
Let’s say you have a reference START DATE like “February 2, 2005″. You would like to calculate the exact WORKDAY 100 days BEFORE or AFTER that date.
How would you do it?
What’s more, how would you do it if the “weekend” definition varied, like “Sunday only” or “Saturday and Sunday”, or even just “Monday”?
Plus, what if there were also HOLIDAYS that you needed to take into consideration as well?
MS Excel offers a comprehensive solution to this problem through the WORKDAY.INTL function.
Here is the general syntax of this versatile date function:
WORKDAY.INTL(start_date, days, [weekend], [holidays])
NOTE: the result is always given in SERIAL NUMBER form. For example, instead of “June 22, 2005″ MS excel would display “38525″. You need to convert that into the conventional date format by going to the Format window (Ctrl+1).
Codes for User-Define WEEKEND days:
| WEEKEND-NUMBER | WEEKEND DAYS |
|---|---|
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Let’s look at a few cases:
SIMPLE CASE: Saturday and Sunday as default weekends and no holidays.
NOTE: Our reference date “February 2, 2005″ is in cell A2.
The formula for Start Date + 100 days is
WORKDAY.INTL(A2,100,1)
- Result is “June 22, 2005″.
The formula for Start Date – 100 days is
WORKDAY.INTL(A2,-100,1)
- Result is “September 15, 2004″.
CASE 2: Saturday and Sunday as default weekend and ALL holidays deducted.
NOTE: Holidays are listed in cell range A6:A9.
The formula for Start Date + 100 days + ALL HOLIDAYS is
=WORKDAY.INTL(A2,100,1,A6:A9)
- Result is “June 23, 2005″
The formula for Start Date – 100 days + ALL HOLIDAYS is
=WORKDAY.INTL(A2,-100,1,A6:A9)
- Result is “September 14, 2004″
CASE 3: Sunday ONLY as default weekend and no holidays.
The formula for Start Date + 100 days (SUNDAY only as weekend) is
WORKDAY.INTL(A2,100,11)
- The result is “May 30, 2005″.
The formula for Start Date – 100 days (SUNDAY only as a weekend) is
WORKDAY.INTL(A2,-100,11)
- The result is “October 8, 2004″
CASE 4: Sunday ONLY as default weekend and ALL holidays deducted.
The formula for Start Date + 100 days (SUNDAY only as weekend + ALL holidays deducted) is
WORKDAY.INTL(A2,100,11,A6:A9)
- The result is “May 31, 2005″.
The formula for Start Date – 100 days (SUNDAY only as weekend + ALL holidays deducted) is
WORKDAY.INTL(A2,-100,11,A6:A9)
- The result is “October 5, 2004″
Here is the total worksheet (click to enlarge it):
BONUS MATERIAL:
You can define the days of the weekend by using BINARY NOTATION as well.
MS Excel uses SEVEN BINARY DIGITS, each representing a day of the week, starting with MONDAY.
1 represents a non-workday and 0 a workday.
If let’s say you’d like to define MONDAY as a non-workday (i.e., weekend day) and all others as a workday, you use “1″ for Monday in the first position and zeros for all the other days of the week.
Thus, “1000000″ represents MONDAY. ”0000011″ represents Saturday and Sunday as the weekend. Etc.
Example:
The following two expressions yield the same result. They both exclude MONDAY as a weekend day and assume no holidays:
WORKDAY.INTL(A2,100,12) = WORKDAY.INTL(A2,100,”1000000″)
NOTE: “1111111″ is an invalid string.
