Excel WORKDAY function
If you want to follow along with this tutorial, please download the example spreadsheet.
Description
The WORKDAY is used to add a specified number of workdays to a started date and returns the future or past date in a serial number format. Weekends and the holidays you specified are excluded when calculation. This function usually calculates the ship dates in Excel.
Syntax and arguments
Formula syntax
| WORKDAY(start_date,days,[holidays]) |
Arguments
|
Return Value
The WORKDAY function returns a date in serial number format.
Remarks
1. In this function, the weekends are defined as Saturday and Sunday. If you want to use different weekend, you can try the WORKDAY.INTL function.
2. If the argument days is a decimal value, the WORKDAY function only add the integer number to the start_date.
3. While using a range of cells as argument holidays, the absolute reference or a range is recommended. Otherwise, when you drag fill handle over cells to copy a formula, the reference will be changed.
Usage and Examples
Example 1 Calculate the future or past workday without holidays
To get the workday based on the start date in column B and adding days in column C, please use below formula:
=WORKDAY(B3,C3)
Press Enter key to get the workday in a serial number format, drag fill handle down to the cells you need this formula.
Then select the serial numbers, click Home > Number Format > Short Date or Long Date to format the serial numbers to date format.
In the formula =WORKDAY(B3,C3), B3 is the start_date, C3 is the days.
Example 2 Calculate the workday excluding holidays
To get the workday based on the start date in column B and adding days in column C and at the same time, excluding the holidays list in column F, please use below formula:
=WORKDAY(B4,C4,$F$3:$F$5)
Press Enter key to get the workday in a serial number format, drag fill handle down to the cells you need this formula.
Then format the serial numbers as date format.
Relative Functions:
-
Excel Days Function
To calculate the number of days between two given dates, the DAYS function in Excel can help you.
-
Excel DAYS360 Function
DAYS360 function can help you to return the number of days between two dates based on a 360-day year, which is considered to have 30 days in each month.
-
Excel WEEKNUM Function
The WEEKNUM in Excel returns the week number of the given date in a year, which starts counting weeks from January 1.
-
Excel DAY Function
With the DAY function, you can quickly get the day as a number from 1 to 31 based on the given dates.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
