Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

WORKDAY Function

Returns a date that is N working days in the future or in past

What is the WORKDAY Function?

The WORKDAY function is categorized under Date and Time functions. It will provide us with a date that is N working days in the future or in past. We can also use the WORKDAY function to add or subtract days to a given date.

In financial analysis, WORKDAY will be useful in calculating the number of workdays worked by employees, the due date of invoices, the delivery time expected for an order, or a debtor aging schedule.

 

Formula

=WORKDAY(start_date, days, [holidays])

 

The function uses the following arguments:

  1. Start_date (required function) – It is a date that represents the start date.
  2. Days (It is a required function) – It is the number of workdays to be added to start_date.
  3. [holidays] (optional argument) – It specifies an array of dates that are not included as workdays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

 

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.

 

The WORKDAY function will exclude weekends, that is Saturday and Sunday. If we need to customize which days of the week are considered weekend days, use the more robust WORKDAY.INTL function.

 

How to use the WORKDAY Function in Excel?

The WORKDAY function was introduced in Excel 2007 and is available in all subsequent Excel versions. In earlier versions, we need to enable the Analysis ToolPak.

To understand the uses of the function, let us consider a few examples:

 

Example 1

Suppose we wish to generate a series of dates that are workdays, which is from Monday to Friday.

We need to generate from 01/15/2018. The formula to use will be:

 

WORKDAY Function

 

We inserted the start date in A2 and then used the formula in A3. Later, we need to simply drag the formula down. We get the results below:

 

WORKDAY Function - Example 1

 

MS Excel will solve this formula by using the WORKDAY function to return the next workday. WORKDAY is programmed to skip forward as needed to the next business day.

As we can see in the above results, it skipped weekends (January 20 and 21) and provided January 22 as a workday. If we want to take into account holidays, we need to add the optional argument to the WORKDAY function for holidays.

 

Example 2

The WORKDAY function can be useful in getting the next business day or next working day.  Suppose we are given the following data:

 

WORKDAY Function - Example 2

 

The formula used was:

 

WORKDAY Function - Example 2a

 

Here, we provided Holidays as the named range holidays (E3:E5), so holidays are taken into account as well. Excel would be 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 Function - Example 2b

 

Few notes about the WORKDAY Function

  1. If we wish to take customize weekends, we need to use WORKDAY.INTL.
  2. #NUM error – Occurs when the start_date provided and the given days argument result in an invalid date.
  3. If the days provided are not an integer, they would be truncated.
  4. 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.
  5. #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.

 

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. 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!