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. preparation, workday scheduling, computation of the number of workdays of an employee, and other financial analysis.
Table of contents
WORKDAY Formula in Excel
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.
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.
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:
The start_date is the date value in cell B2, the number of days in cell B3, and holidays E2:E11.
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:
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.
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.
We will again use the WORKDAY in Excel function. The WORKDAY formula in Excel will be:
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:
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 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. together to generate the random working days.
The WORKDAY formula will be:
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:
Things to Remember
- If we want to customize the Weekends (other than Saturday and Sunday), we must use the WORKDAY.INTL function.
- 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.
- 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. 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.
- If the start date is invalid, the WORKDAY function will generate #VALUE! Error.
- 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. 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: –
- VBA Date FunctionVBA Date FunctionVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date.
- INT Excel Function (Integer)INT Excel Function (Integer)INT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.
- MONTH Formula in ExcelMONTH Formula In ExcelThe Month Function is a date function that determines the month for a given date in date format. It takes an argument in a date format and displays the result in integer format.
- LN Function in ExcelLN Function In ExcelLN function calculates the logarithm of a given number which takes a single number as the argument and gives the result as the logarithm.