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.

Supported versions

  • Excel 2007 and newer

Syntax of WORKDAY Function

WORKDAY(start_date, days, [holidays])

Arguments

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.

Future date: =WORKDAY("1/28/2021",5)

Past date: =WORKDAY("1/28/2021",-5)


WORKDAY Function

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.

=WORKDAY(B13,C13,B18:C18)

WORKDAY

Download Workbook

Tips

  • 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.

Issues

  • If text arguments are not valid dates, the WORKDAY function returns #VALUE! error value. Use the DATE function instead.