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

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.