WORKDAY Excel Function

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

The WORKDAY function in Excel is a DATE function used to calculate the work date coming after a given number of days from the start date. In addition, this function has an optional argument for holidays which, if not provided, automatically considers the weekends, Saturdays, and Sundays, as holidays and calculates the future work date after a given number of days. The method to use this function in Excel is as follows =WORKDAY ( Start_date, Days, Holidays). The first two arguments are mandatory; however, the argument for holidays is optional.

For example, =WORKDAY(A1,B1). A1-1/01/2020 (starting_date), B1- 45 days(days for completion)which gives 4-03-2020 in cell C1.

WORKDAY Function in Excel

A WORKDAY function is categorized as a DATE/TIME function in Excel that returns the date after or before a specified number of working days, excluding the weekends and holidays.

A workday in Excel is a very efficient function used for calculating working days, due dates, Excel Gantt chartsExcel Gantt ChartsGantt chart is a type of project manager chart that shows the start and completion time of a project, as well as the time it takes to complete each step. The representation in this chart is shown in bars on the horizontal axis.read more preparation, workday scheduling, computation of the number of workdays of an employee, and other financial analysis.

WORKDAY Formula in Excel

The WORKDAY formula in Excel is as follows:

WORKDAY Formula in excel

Arguments used for WORKDAY Formula in Excel.

start_date: It is the date from which we want to start. It is a required field.

days: It is the number of working days before or after the start_date. This value could be negative or positive. When a negative value is passed, the WORKDAY in Excel returns a paste date. If the value is positive, it gives the future date.

holidays: This is an optional field passed when we want to exclude the holidays or weekends. It can be passed as a single or multiple range value.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

WORKDAY Excel Function Video

How to Use WORKDAY Function in Excel?

It is very simple and easy to use. Let us understand the working of the WORKDAY function in some examples.

You can download this WORKDAY Function Excel Template here – WORKDAY Function Excel Template

Example #1

WORKDAY Example 1

Example #2

Consider the following scenario: a company received an order to manufacture a piece of machinery, and the assigned team started the project on a specified date. It is assumed that the manufacturing process will consume 35 days. Further, the company does not operate on weekends and holidays. Given these variables, you must calculate the project’s estimated completion date.

WORKDAY Example 2

The start_date is in cell B2, the number of production days is in cell B3, and the holidays with their dates are in the range D2:E11.

WORKDAY Example 2-1

We will use the WORKDAY function to calculate the estimated completion date. The WORKDAY formula will be:

=WORKDAY(B2,B3,E2:E11)

WORKDAY Example 2-2

The start_date is the date value in cell B2, the number of days in cell B3, and holidays E2:E11.

WORKDAY Example 2-3

Example #3

Suppose we have a date, and we want a series of consecutive dates in adjacent row cells below, excluding the non-working days that are Monday to Friday only. For example, suppose in cell B2, we have a starting date (2/19/2018). We want dates 2/20/2018, 2/21/2018… so on. We want to exclude dates like 2/24/2018 and 2/25/2018, which are the weekends, and we want all the current month’s dates.

WORKDAY Example 3

We will use the Workday function, and the days will be 1 as we want to add consecutive days without passing the last argument, which is optional and for holidays.

So, the WORKDAY formula will be:

=WORKDAY(B2,1)

Applying the WORKDAY formula to other cells we have,

WORKDAY Example 3-1

The WORKDAY formula returned all the dates, excluding the weekends of the current month.

WORKDAY Example 3-2

As we can see above, the WORKDAY function neglected the days (02/24/2018, 02/25/2018) that were weekends.

Example #4

Suppose we have a list of working days dates given in a column. We want to determine if a given date is a holiday. We want to return “Working Day” if the date is a workday and holiday if the date is a holiday. A list of holidays and their dates are mentioned within range D3:E4.

WORKDAY Example 4

Holidays:

Example 4-1
Example 4-2

We will again use the WORKDAY in Excel function. The WORKDAY formula in Excel will be:

=IF(WORKDAY(A2-1,1,$E$3:$E$4)=A2,”Working Day”,”Holiday”)

Formula WORKDAY(A2-1,1) returns the same date since we subtract the date and add a date again. So, we have the same date if the output is the same as the given date. Therefore, it is a working day because the WORKDAY formula skips the holidays.

Example 4-3

Applying the WORKDAY formula in Excel to other cells, we have:

Example 4-4

Example #5

Suppose we have two dates given. We have to generate 10 random days between these two given dates, excluding the Saturday and Sunday weekends. The given dates are:

WORKDAY Example 5
Example 5-1

We will use the RANDBETWEEN functionRANDBETWEEN FunctionRANDBETWEEN excel formula determine random numbers between two extreme variables (bottom and top numbers). The user needs to fill in the bottom and top numbers in the syntax =RANDBETWEEN (bottom, top) to acquire the random integer.read more together to generate the random working days.

The WORKDAY formula will be:

=WORKDAY(RANDBETWEEN($B$2,$B$3)-2,1)

We subtract minus two, so we do not get the random working day above or equivalent to the end date value.

Applying the WORKDAY formula in Excel in other cells, we have:

Example 5-2

Output:

Example 5-3

Things to Remember

  1. If we want to customize the Weekends (other than Saturday and Sunday), we must use the WORKDAY.INTL function.
  2. Suppose we provide the third argument to the WORKDAY function for holidays as a range. In that case, we must pass it as an absolute value because the value of the range may change when we apply the same WORKDAY formula to other cells in the sheet.
  3. The WORKDAY function generates a #VALUE error#VALUE Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more when a start_date or any of the values provided in the holiday’s range are not valid dates or if the given day’s argument is non-numeric.
  4. If the start date is invalid, the WORKDAY function will generate #VALUE! Error.
Example 5-4
  1. When we use the WORKDAY function, it is required that we pass the start_date or holidays as a reference to a cell with a date value instead of entering them as text values within quotes because excel may misinterpret the date values. So, instead of directly using the date as a text value, we should use the DATE function in excelDATE Function In ExcelThe date function in excel is a date and time function representing the number provided as arguments in a date and time code. The result displayed is in date format, but the arguments are supplied as integers.read more to enter a date so that the EXCEL may always understand it as a date value.

This article has been a guide to the WORKDAY Function in Excel. Here, we discuss the WORKDAY formula in Excel and how to use the WORKDAY function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *