Excel WORKDAY function

doc workday function 1

If you want to follow along with this tutorial, please download the example spreadsheet.

doc sample

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])

doc workday function 2

Arguments

  • Start_date: Required. The start date used to calculate the workday.
  • Days: Required. A specified number of workdays that you want to add to the start date. It can be negative or positive. If you want to calculate the workday in past, using negative number, if return to the future workday, using positive number.
  • Holidays: Optional. A list of dates or an array constant of serial numbers which are considered as non-workday.

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.
doc workday function 3

Then select the serial numbers, click Home > Number Format > Short Date or Long Date to format the serial numbers to date format.
doc workday function 4
doc workday function 5

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.
doc workday function 6

Then format the serial numbers as date format.
doc workday function 7


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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.