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.
=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
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.
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)
Notice how the date when the project will be complete has now been extended two days.
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.