How to use the WORKDAY function
What is the WORKDAY function?
The WORKDAY function returns a date based on a start date and a given number of working days (nonweekend and nonholidays).
What is a workday?
A weekday that is not a weekend day or holiday. Typically Monday through Friday excluding any holiday dates.
Use the WEEKDAY function to determine if a date is a workday or a weekend day.
What is a holiday?
A day on which regular activities and work is suspended due to a cultural, religious or legal custom. Varies by country and region.
WORKDAY function Syntax
WORKDAY(start_date, days, [holidays])
WORKDAY function Arguments
start_date | Required. |
days | Required. Positive integer returns a date after the start_date (future) and negative integer returns a date before the start_date. |
[holidays] | Optional. A list of holiday dates. |
WORKDAY function not working
The WORKDAY function returns
- #VALUE! error value if start_date or [holidays] are not a valid date.
- #NUM error if start_date plus days argument returns an invalid date.
(Excel can't handle dates before 1/1/1900.)
Use the DATE function to create valid Excel dates.
Example:
What is an Excel date?
An Excel date is a serial number that Excel recognizes and can be filtered, sorted and used in other date calculations.
Excel dates are actually serial numbers formatted as dates, 1/1/1900 is 1 and 2/2/2018 is 43133. There are 43132 days between 2/2/2018 and 1/1/1900.
You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.
WORKDAY function example
This example demonstrates how to use the WORKDAY function to calculate a future date with the condition of being a workday excluding weekend days and holidays.
The start date is shown in cell C3 in the image above. The number of working days days is 12 and the function returns 1/17/2018. The formula in cell B8 is shown below, note that no holidays has been specified.
Formula in cell B8:
The image below shows the start date and the weekdays. The count is entered below dates that are workdays.
Note that there are no numbers below weekend days because they are not workdays. Counting 12 days after the start date and we get 1/17/2018.
WORKDAY function example - Negative days argument and a holiday
This example demonstrates the WORKDAY function with a negative day argument which means it calculates a workday before the start date instead of after the start date.
The start date is shown in cell C3 in the image above. The number of working days days is -12 and the function returns 12/13/2017. The formula in cell B8 is shown below, note that holiday 12/25/2017 has been specified.
Formula in cell B9:
This example also shows no numbers below weekend days because they are not workdays, this applies to 12/25/2017 as well because it si specified as a holiday. Counting 12 days befoe the start date and we get 12/13/2017.
'WORKDAY' function examples
The following article has a formula that contains the WORKDAY function.
The image above shows the performance across industry groups for different date ranges, conditional formatting makes the table much easier […]
Functions in 'Date and Time' category
The WORKDAY function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form