WORKDAY function in excel is a date function which is used to calculate the work date coming after a given number of days from a start date, this function has an optional argument for holidays which if not provided automatically considers the weekends which is Saturday and Sundays as holidays and calculates the coming workdate 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.
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.
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
day: 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, 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 a single or multiple range value.
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, and it is assumed that the manufacturing process will consume 35 days. Further, the company doesn’t operate on weekends and holidays. Given these variables, 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 the range D2:E11.
To calculate the estimated completion date, we will be using the Workday function, and the WORKDAY formula will be:
The starting 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. 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 which are the weekends, and we want all the dates of the current month.
We will be using the Workday function, 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 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, 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.
We will again use the Workday in excel function, and the WORKDAY formula in excel will be,
Formula WORKDAY(A2-1,1) returns the same date since we are subtracting 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.
Applying the WORKDAY formula in excel to other cells we have,
Suppose we have two dates given, and we have to generate 10 random days between these two given dates, excluding the weekends that are Saturday and Sunday. The given dates are:
In this case, to generate the random working days, 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. and WORKDAY function together.
The WORKDAY formula will be
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
Things to Remember
- In case if we want to customize the Weekends (other than Saturday and Sunday), we have to use WORKDAY.INTL function.
- If we provide the third argument to WORKDAY function 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 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 an invalid date, 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 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 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 as a Date Value.
WORKDAY Excel Function Video
This 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 example and downloadable excel templates. You may also look at these useful functions in excel –