This Excel tutorial explains how to use the Excel WORKDAY function with syntax and examples.
Description
The Microsoft Excel WORKDAY function adds a specified number of work days to a date and returns the result as a serial date. Weekends are not considered work days and you can specify holidays to also exclude from the work day calculation.
The WORKDAY function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the WORKDAY function can be entered as part of a formula in a cell of a worksheet.
If you want to follow along with this tutorial, download the example spreadsheet.
Download Example
Syntax
The syntax for the WORKDAY function in Microsoft Excel is:
WORKDAY( start_date, days, [holidays] )
Parameters or Arguments
- start_date
- The starting date to use in the calculation and should be entered as a serial date, not a text date.
- days
- The number of work days added to start_date.
- holidays
- Optional. It is the list of holidays to exclude as work days from the calculation. It can be entered either as a range of cells that contain the holiday dates (ie: G2:G5) or as a list of serial numbers that represent the holiday dates.
Returns
The WORKDAY function returns a serial date value. A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.
Note
- Weekends are defined as Saturday and Sunday. If you want to use different days as the weekend, try the WORKDAY.INTL function.
- If a decimal value is provided for days, the WORKDAY function will only add the integer portion to start_date.
- If you enter a range of cells for the holidays, enter the range as an absolute value so that the range does not change when copying the formula to different cells. For example, if your holidays are stored in the range G2:G5, it is best to enter the range as $G$2:$G$5.
Applies To
- Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007
Type of Function
- Worksheet function (WS)
Example (as Worksheet Function)
Let's look at some Excel WORKDAY function examples and explore how to use the WORKDAY function as a worksheet function in Microsoft Excel:
The result of the WORKDAY function is a serial date and can be seen in column C above.
Most often you will want a formatted date rather than a serial date. Column D demonstrates the serial date returned by the WORKDAY function that has been formatted using mmm d, yyyy.
Enter Holidays as a Range
The holidays parameter is optional and is usually entered as a range of cells. In our example, holidays are found in the range of $G$2:$G$5.
Based on the Excel spreadsheet above, the following WORKDAY examples would return:
=WORKDAY(A2,B2,$G$2:$G$5)Result: 42369 'Which can be formatted as "Dec 31, 2015"=WORKDAY(A3,B3,$G$2:$G$5)Result: 42373 'Which can be formatted as "Jan 4, 2016"=WORKDAY(A4,B4,$G$2:$G$5)Result: 42458 'Which can be formatted as "Mar 29, 2016"=WORKDAY(A5,B5,$G$2:$G$5)Result: 42464 'Which can be formatted as "Apr 4, 2016"
Enter Holidays as Text Dates
Holidays do not have to be entered as a range of cells. They can also be entered as text dates. However how you enter text dates depends on whether you enter one date or more than one date.
For example, here is how you would enter a single date as a holiday:
=WORKDAY(A2,B2,"1/1/2016")Result: 42369 'Which can be formatted as "Dec 31, 2015"
This formula would treat Jan 1, 2016 as a holiday and exclude it from the work day calculation.
If you want to enter more than one holiday as a text date, you will need to enter the dates as an array.
For example:
=WORKDAY(A5,B5,{"1/1/2016","3/25/2016","3/28/2016","12/25/2016"})Result: 42464 'Which can be formatted as "Apr 4, 2016" This WORKDAY function would treat 1/1/2016, 3/25/2016, 3/28/2016 and 12/25/2016 as holidays and will not include them as work days.
TIP: Just be careful when entering your dates as text values because the format of the date will depend on your Regional Settings. In our example, our Regional Settings are set to a date format of mm/d/yyyy. It is always safer to use serial dates if you are unsure.
Enter Holidays as Serial Dates
Finally, let's explore how to enter holidays as serial dates. Entering the holidays as serial dates ensures that Excel processes the dates properly. Remember that how you enter a holiday as a serial date depends on whether you are entering one date or more than one date.
For example, here is how to enter a single serial date for holidays:
=WORKDAY(A2,B2,42370)Result: 42369 'Which can be formatted as "Dec 31, 2015"
Since 42370 is the serial date for Jan 1, 2016, that date will be excluded from the work day calculation.
If you want to enter more than one holiday as a serial date, you need to include your serial dates as an array.
For example:
=WORKDAY(A5,B5,{42370,42454,42457,42729})Result: 42464 'Which can be formatted as "Apr 4, 2016" Since 42370 is 1/1/2016, 42454 is 3/25/2016, 42457 is 3/28/2016 and 42729 is 12/25/2016, these dates will not be included as work days.
TIP: To see the numeric value for any serial date, change the format of the cell to General. It will then display the serial date value.
