Latest posts by techwriter (see all)
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016
- How to View a List of All Open MS Word Documents through VBA Macro - August 31, 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.