In this blog, we would learn the extended and advanced version of the WORKDAY formula called the WORKDAY.INTL function in Excel. This tutorial would cover the following:
- Purpose of WORKDAY.INTL function in excel
- Difference between Excel WORKDAY and WORKDAY.INTL function
- Syntax and Arguments
- Examples of WORKDAY.INTL excel function
So, let us begin with this tutorial. 😎
Objective of WORKDAY.INTL Function in Excel
Just like WORKDAY function, the =WORKDAY.INTL formula in excel is used to find a date which is either after or before a specific number of working days. This formula also allows you to specify the list of holiday dates. These holiday dates are excluded while calculating the number of days.
There are many practical applications of this formula, for example, one can calculate invoice due date based on the due date and credit period using this formula.
Let me give you one example to understand the purpose of the function better. Suppose, a company sells goods to its customer with an invoice date 14-02-2021 and also provides a credit of 45 days for payment. So, Invoice due date = invoice date + credit period.
The WORKDAY.INTL formula is very helpful in this case to find invoice due date.
WORKDAY vs WORKDAY.INTL
The purpose of the WORKDAY formula and the WORKDAY.INTL formula are almost the same.
However, unlike the former, using the WORKDAY.INTL function you can use weekends as days other than Saturdays and Sundays. This means that you can tell excel which day(s) of the week should it consider as a weekend. For example, instead of having Saturdays and Sundays as weekends, you may use Tuesdays and Wednesdays or even only Tuesdays.
The weekend days that you specify in this formula are excluded from days calculation.
Syntax and Arguments
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
As you can see in the above formula syntax, the WORKDAY.INTL formula has one additional argument which is [weekend]. Below is the explanation of the formula arguments:
- start_date – Enter the starting date (i.e. invoice date) in this argument, manually or as a cell reference.
- days – Specify the number of days you want to add or subtract from the start_date. To add days, enter the argument as a positive number, and to go backward enter the days with a minus (-) sign before it (i.e. negative number).
- [weekend] – The [weekend] argument is an optional argument. In this argument, select the days from the list that you want to consider as weekend(s). If you leave the argument as blank, excel would consider Saturdays and Sundays as weekends, by default.
- [holidays] – The [holiday] is the last and an optional argument of the =WORKDAY.INTL function. Here, you need to specify the reference of the range of cells containing the list of holiday dates.
Examples of WORKDAY.INTL Function in Excel
Let us take some examples of excel WORKDAY.INTL formula to understand about how it is different of WORKDAY function.
1# WORKDAY.INTL Function (Without Holiday List)
Let us consider the same example. Invoice date = 14-02-2021 and credit term = 45 days.
In this case, for calculating invoice due date, simply use the following formula:
As a result, you would get the invoice due date as 16-04-2021.
In the above example, to calculate the date before a certain number of days (45 days), then use negative 45 (-45) as the second argument. See the image below:
2# WORKDAY.INTL Function (With Holiday List)
Now, let us consider the same example with some holidays. In this case, the fourth argument of the formula will be entered as a range of cells. See the image below:
Note that we have not kept the third argument [weekend] as blank. It means that excel will by default consider Saturdays and Sundays as weekend days.
3# WORKDAY.INTL Function With Different Weekend Days
Now, let us consider another example to calculate a date after certain number days with weekend days different than Saturdays and Sundays.
While entering the formula into the cell, as soon as you reach to the third argument, excel would propose the list of weekend values as below:
You can select the weekend of your choice from the given list. See example in the image below:
Do Not Miss These Points
- When you specify the start_date inside the function argument manually, then it must be put within double-quotes.
- Instead of entering date manually as function argument, you can also give a reference of some other cells as shown in the image below: