Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY

Add to Favorites
Author:

How to find a date that is so many days before or after a specified date, excluding weekends and any holidays.

This allows you to get an accurate estimate of when work will be completed, a package delivered, invoicing, and more.

To achieve this, we use the WORKDAY() function in Excel.

Syntax

=WORKDAY(start_date, days, [holidays])

Argument

Description

Start_date

The starting date of the calculation.  It is best to reference a cell that has a date in it for this argument.  If you want to enter the date directly in here, use the DATE function to do so.

 Days

The number of working days (days that are NOT weekends and NOT holidays) before or after the start date.  A negative number will return a past date and a positive number will return a future date. This argument is, for instance, how many working days a project will take you.

 [Holidays]  This argument is optional. It is a list of days that will not be worked.  You can enter this as an array or, much easier, reference a table of dates for all of the holidays.

[] means the argument is optional

Simple WORKDAY Example

Let's get the date a project will be complete that is started on January 4, 2016 and that will take 30 work days to complete.

  1. Type =WORKDAY( where you want to find out the date.
  2. Select the cell with the starting date, where January 4, 2016 is located.
  3. Type a comma and then select the cell that contains the number of days the project will take or simply type this number directly into the WORKDAY function.
  4. Hit enter.
  5. Select the cell with the funny number and format it as a date.

    The weird number, 42415, is simply a representation of a date in Excel.  Once we format that number as a date, everything will look like we expect it to look.
  6. That's it!

WORKDAY Example with Holidays

We can also include a list of holidays with this function.  The purpose of this is to tell the function the days that no one will be working so that it can be taken into account.

(this process is almost the same as the previous example)

  1. Create a list of holidays with their corresponding dates.  I've done this on the same worksheet to make it easier to follow but, in practice, you should keep this on a separate worksheet.
  2. Type =WORKDAY( into the cell where you want the result.
  3. Select the cell that contains the starting date.
  4. Type a comma to move to the next argument and then select the cell that contains the number of days.
  5. Type a comma to move to the Holidays argument.  Here, we will select the range of dates that are the holidays.
  6. Hit Enter.
  7. Format the cell as a date.
  8. That's it!

Notice how the date when the project will be complete has now been extended two days.

Notes

This is a simple but VERY helpful function.  When you combine this function with a maintained and up-to-date list of holidays for your company, you can make very accurate and useful calculations.

If you want to get the number of days that a project will take you, check out our NETWORKDAYS tutorial.

If your weekends are not the standard Saturday and Sunday, use the WORKDAY.INTL function.

Make sure to download the spreadsheet included with this tutorial so you can play around with this function and better understand how to use it.


Excel Function: WORKDAY()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: WORKDAY()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course