Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

WORKDAY.INTL Function

Returns a date that is N working days in the future or in the past with custom weekend parameters

What is the WORKDAY.INTL Function?

The WORKDAY.INTL function is categorized under Date and Time functions. It is a robust modification of the WORKDAY function, as it works with customizable weekend parameters. WORKDAY.INTL will provide us with a date that is N working days in the future or in the past but allows us to determine dates that would be weekends. We can also use the function to add or subtract days from a given date.

In financial analysis, the WORKDAY.INTL function will be useful in calculating the number of workdays worked by employees, the due date of invoices, delivery time expected for an order, or a debtor aging schedule. The most important use is that we can customize weekends so we can use it across countries with different weekends.

 

Formula

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

 

The function uses the following arguments:

  1. Start_date (required function) – It is a date that represents start date.
  2. Days (required function) – It is the number of workdays to be added to start_date. If we enter a positive value, it will give us a future date, while a negative value will yield a past date.
  3. Weekend (optional argument) – It indicates which days of the week are weekends and are not to be considered as working days. Weekend is a weekend number or string that specifies when weekends occur. Weekend number values indicate the following weekend days:

 

Weekend numberWeekend days
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

 

Weekend string – It is a series of seven 0’s and 1’s that represents seven days of the week, beginning with Monday. 1 represents a non-working day and 0 represents a workday. For example:

“0000011” – Saturday and Sunday are weekends.

“1000001” – Monday and Sunday are weekends.

  1. [holidays] (optional argument) – It specifies an array of dates that are not included as workdays. The list can either be a range of cells that contain the dates or an array constant of the serial numbers that represent the dates. The ordering of dates or serial values in holidays can be arbitrary.

 

In entering dates, Microsoft recommends that the start_date and [holidays] arguments should be entered as either:

  1. References to cells containing dates; or
  2. Dates returned from formulas

If we input date arguments as text, there is a risk that Excel may misinterpret them, depending on the date system or date interpretation settings on your computer.

 

How to use the WORKDAY.INTL Function in Excel?

The WORKDAY function was introduced in Excel 2007 and is available in all subsequent Excel versions. To understand the uses of the function, let us consider an example:

 

Example

Suppose we wish to add 30 days to a particular workday and subtract 20 days from a workday. The weekends here are Friday and Saturday.

The formula used was:

 

 WORKDAY.INTL Function

 

Here, we provided Holidays as the named range holidays (E6:E8), so holidays are taken into account as well. Excel is concerned only with dates and not with which holiday it is, that is, it is not concerned with the actual name of the holidays.

We get the results below:

 

 WORKDAY.INTL Function - Example 1

 

The above formula tells us the first working day of the month from December 2017 to February 2019.

 

Few notes about the WORKDAY.INTL Function

  1. #NUM! error – Occurs when:
    1. The start_date provided and the given days argument results in an invalid date; or
    2. The given weekend argument is invalid argument.
  2. If the days provided are not an integer, they will be truncated.
  3. MS Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2018 is serial number 43101, because it is 43,101 days after January 1, 1900.
  4. #VALUE! error – Occurs when:
    1. The given start_date or any of the values provided in the [holidays] array are not valid dates.
    2. The given days argument is non-numeric.
    3. The given [weekend] argument is an invalid text string.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and analysis. To learn more, check out these additional resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!