Latest posts by techwriter (see all)
- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
© Ugur Akinci
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:
|1 or omitted||Saturday, Sunday|
Let’s look at a few cases:
SIMPLE CASE: Saturday and Sunday as default weekend and no holidays.
NOTE: Our reference date “February 2, 2005″ is in cell A2.
The formula for Start Date + 100 days is
- Result is “June 22, 2005″.
The formula for Start Date – 100 days is
- 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
- Result is “June 23, 2005″
The formula for Start Date – 100 days + ALL HOLIDAYS is
- 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
- Result is “May 30, 2005″.
The formula for Start Date – 100 days (SUNDAY only as weekend) is
- 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
- Result is “May 31, 2005″.
The formula for Start Date – 100 days (SUNDAY only as weekend + ALL holidays deducted) is
- Result is “October 5, 2004″
Here is the total worksheet (click to enlarge it):
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 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 weekend. Etc.
The following two expression 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.