WORKDAY Excel Function

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 charts preparation, workday scheduling, computation of the number of workdays of an employee, and other financial analysis.

The WORKDAY formula in Excel is as follows:

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.

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

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.

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.

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

=WORKDAY(B2,B3,E2:E11)

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

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.

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,

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

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.

Holidays:

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.

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

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:

We will use the RANDBETWEEN function 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:

Output:

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

5. 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 excel to enter a date so that the EXCEL may always understand it as a date value.