WORKDAY Excel Function (Table of Contents)

• WORKDAY in Excel
• WORKDAY Formula in Excel
• How to Use WORKDAY Function in Excel?

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 in excel for calculating working days, due dates, Gantt charts 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

Arguments used for WORKDAY Formula in Excel.

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

days: it is the number of working day 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, and if the value is positive it gives the future date.

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

How to Use WORKDAY Function in Excel?

WORKDAY in Excel function is very simple and easy to use. Let understand the working of WORKDAY in excel by some examples.

WORKDAY in Excel Example #1

WORKDAY in Excel 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 and it is assumed that the manufacturing process will consume 35 days, further, the company doesn’t operate on weekends and holidays. Given these variable, you need to calculate the estimated completion date of the project.

The Starting date is in cell B2 and the number of production days is in cell B3 and the Holidays with their dates are in range D2:E11

To calculate the estimated completion date, we will be using the Workday in Excel and the WORKDAY formula in excel will be:

=WORKDAY(B2,B3,E2:E11)

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

Hence, the project will complete on 4^th Jan’19.

WORKDAY in Excel 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 is Monday to Fridays only. Suppose in cell B2 we have a starting date (2/19/2018) and we want dates 2/20/2018, 2/21/2018… so on. We want to exclude dates like 2/24/2018, 2/25/2018 that are the weekends and we want all the dates of the current month.

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

So, the WORKDAY formula in excel will be

=WORKDAY(B2,1)

Applying the WORKDAY formula in excel to other cells we have,

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

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

WORKDAY in Excel Example #4

Suppose we have a list of Working days dates given in a column and we want to determine if a given date is a holiday. We want to return ‘Working Day’ if a date is a Workday and ‘Holiday’ if the date is a holiday. List of holidays and their dates are mentioned within range D3:E4.

Holidays:

We will again use the Workday in excel function and 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 are subtracting a date and again adding a date. So we have the same date if the output is the same as the given date it is a working day because the holidays are skipped by the WORKDAY formula in excel.

Applying the WORKDAY formula in excel to other cells we have,

WORKDAY in Excel Example #5

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

In this case, to generate the random working days we will use the RANDBETWEEN and WORKDAY function in excel together.

The WORKDAY formula in excel will be

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

We subtract minus two so that we don’t 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:

Things to Remember About WORKDAY Function in Excel

1. In case, if we want to customize the Weekends (other than Saturday and Sunday) we have to use WORKDAY.INTL function.
2. If we provide the third argument to WORKDAY in Excel for holidays as a range it is necessary that we should pass it as an absolute value because the value of the range may change when we apply the same WORKDAY formula in excel to other cells in the sheet.
3. The Workday in excel 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 days argument is non-numeric.
4. If the start date is an invalid date the WORKDAY function in excel will generate #VALUE! Error.

5. When we use the WORKDAY function in excel it is required that we pass the start_date or holidays as a reference to cell with date value instead of entering them as text value within quotes, because excel may misinterpret the date values. So, instead of directly using the date as an text value we should use DATE function to enter a date so that the EXCEL may understand always as a Date Value.

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

Recommended Articles

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

• SIGN Excel Function
• COUNT Function in Excel
• LN Excel Function
• TAN Excel Function (Tangent)

The post WORKDAY Excel Function appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.