In this blog, we would learn one of the most used excel date functions called the WORKDAY function in Excel. We would cover the objective of the WORKDAY excel formula, along with its syntax and arguments, and some excellent examples for clear understanding.
Here you go 😎
Purpose of WORKDAY Function in Excel
The =WORKDAY formula in excel is used to find and get a date which is either before or after a given number of working days. This formula also allows you to specify the holiday dates to be excluded from the number of days calculation.
For example, suppose today’s date is 12-02-2021. Now, you would like to get the date which is 50 working days after today. In that case, the WORKDAY function of excel is very useful. Similarly, you can use this function to find the date which 50 days before today.
The practical applicabilities of this function are manifold. For example, you can find invoice due date in excel using the WORKDAY function, where invoice due date = invoice date + a number of working days (credit period).
Syntax and Arguments
=WORKDAY(start_date, days, [holidays])
There are three input arguments of WORKDAY excel formula
- start_date – In this argument, enter the starting date.
- days – Here, specify the number of working days that you want to add or deduct from the start_date. Enter a positive number if you want to get a date after the start_date. Likewise, enter a negative number if you want to get a date before the start_date.
- [holidays] – The [holidays] argument is an optional input argument. Here, specify the cell range that contains a list of holiday dates. Excel excludes the holiday dates specified in this argument from the calculation. You can use this argument to specify any national holiday, public holiday, or bank holidays.
Note that, excel does not include weekends for counting purposes. By default, weekends are Saturdays and Sundays.
Examples of WORKDAY Function in Excel
Let us take some examples to understand how the WORKDAY function works.
#1 WORKDAY Function Without Holiday List
Suppose you have issued an invoice to your customer dated 14-01-2020 with a credit period of 45 working days. Assume that there are no holidays.
To find the due date of the invoice in excel using invoice date (14/01/2020) and credit period (45 days), simply use the following formula:
The above formula would return the result as 17-03-2020, excluding the Saturdays and Sundays.
Similarly, you can go reverse or backward by putting minus sign (-) before the days.
#2 WORKDAY Function With Holiday List
Now, let us consider a list of holidays, and calculate the day after a particular date excluding weekends and listed holidays.
Suppose you have issued an invoice to your customer dated 14-01-2020 with a credit period of 45 working days. Assuming there are three holidays in between listed in cells A2 to A4, as shown below:
To count the date after or before a specified date by excluding the list of holidays in excel, simply enter the reference of holiday list cell range as the third argument of the WORKDAY function.
Do Not Miss These Points
- If you are specifying the start_date manually, it must be put within double-quotes. See examples shown above.
- Instead of manually entering start_date as input argument, you can also give reference to some cell containing the starting date, as shown below.
- Another way is to use the date serial number instead of entering the date in DD-MM-YYYY format. A serial number is a whole number representing a date. Excel stores and interprets the dates as whole numbers. Like it stores the date 14/01/2020 as 43844. Similarly, 15/01/2020 is 43845 (i.e. 43844 + 1).
Finally, we have completed this tutorial on WORKDAY Function in Excel 🙂