WORKDAY is a Date & Time function that can add or subtract the number of workdays from a given date. In this guide, we’re going to show you how to use the WORKDAY function and also go over some tips and error handling methods.
- Excel 2007 and newer
Syntax of WORKDAY Function
|start_date||The start date to add or subtract days|
|days||The number of dates before or after the start_date.|
|[holidays]||Optional. A range of date values to be excluded in addition to weekends. An array constant is valid as well.|
Examples of WORKDAY Function
Only weekends excluded
To calculate the date before or after a certain date (excluding the weekends), begin by providing the start_date and days arguments into the formula. Both date values can be a string, a serial number, or a calculated date by a formula (e.g. DATE).
In the following examples, we are adding 5 days to the start date, and also subtract 5 days from it. If it wasn't for the WORKDAY formula, we would get 2/2/2021 and 1/23/2021 instead. Since the WORKDAY function doesn’t take weekends into the calculation. we get see 2/4/2021 and 1/23/2021.
Past date: =WORKDAY("1/28/2021",-5)
Excluding weekends and holidays in WORKDAY Function
You can provide an array of dates as [holidays] arguments to exclude specific dates along with weekends. The [holiday] argument can be a range of cells or a constant array. The following example demonstrates scenarios both excluding and including holiday dates.
- Prefer using the WORKDAY.INTL function to calculate working days by determining how many days are weekends.
- WORKDAY function ignores time value of dates.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
- If text arguments are not valid dates, the WORKDAY function returns #VALUE! error value. Use the DATE function instead.