How to use the WORKDAY.INTL function in Excel

In this article, we will learn How to use the WORKDAY.INTL function in Excel.

How does WORKDAY function in excel actually work?

Assume, in a HR office, there is a plan that they will give surprise gifts to the employees when they complete their first 365 workdays. The HR manager gives a task to find out each employee's 365th working day from the date of joining. The office doesn't consider national holidays weekends and other non working days for all employees. For this kind of problem you can use the WORKDAY function in Excel. The WORKDAY function in excel takes the start date, number of days and the list of non working days. The function considers weekends as default Saturday and Sunday. If you want to define the weekend  or customize the non working days in a week, then use the extended version of WORKDAY function which is WORKDAY.INTL function. Learn more about how to use the WORKDAY.INTL function in Excel as stated below.

WORKDAY.INTL Function in Excel

WORKDAYS.INTL function is a date and time function which returns the working date as a number. It takes start_date and days as arguments which will decide the count of days before or after the start date. There are two optional arguments along given dates.

1. Weekends : days which are non-working in a week
2. holidays : random non working days to include.

WORKDAY.INTL Function syntax:

start_date : counting start date

days : number of days to shift before and after the start date

[weekend] : [optional] days of week considered as weekends. Default is 1 (Saturday & Sunday)

[holidays] : [optional] array reference of says considered as non working days.

More about Weekend argument :

Weekend argument takes the number argument which denotes the pattern of non working days in a week. Default is 1 (Sat & Sun as weekend).  For more inspect the below table

The above shown shows the default numbers which can be selected from the popular non working days. 

You can customize the type of non working days using masking week days. Provide the 7 digit weekend argument with quotes representing 1 means weekend and 0 means workday. For Example if the non-working days in the week are Tuesday, Wednesday, Saturday and Sunday then mask argument will be "0101011" starting from monday. 

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have some sample dates where we need to get the date before or after the start date.

Use the formula:

As you can see the adding 28 working days to 01-01-2020, we get the 10-02-2020 as the working day. This formula includes Sat & Sun as default weekend and no national holiday is considered. Now use these two arguments to learn this function in a better understanding. In the below example list of national holidays are taken into account using the named range reference holidays.

Use the formula:

As you can see, if we go back 68 working days from the date 24-02-2020, we get 03-12-2019 of the last year as the working date. In the above example we choose only Sunday as the weekend using 11 as default argument and national holidays are considered.

Now we use a masking method for the weekend argument. In the below example, we consider no weekend in a week and no national holiday.

Use the formula:

0 : for masking working day starting from Monday

As you can see, the function returns 16-10-2020. You can get the same result adding 50 to the date value directly. Masking must be done using the 0s and 1s notation with quotes. Let's understand one more example of masking weekend.

Use the formula:

0 : for masking working day starting from Monday

1 : for masking non-working day starting from Monday

As you can see the use of masking to get different types of non working days in a week as weekends. Similarly, you use this function to calculate the working date from the start date considering only workdays between the dates.

As you can see the formula in the formula box. WORKDAY.INTL is an extended version of the WORKDAY function which lets you edit the weekend in a week. You can use the WORKDAY function if the weekend days are fixed as Saturday and Sunday. 

Here are all the observational notes using the WORKDAY.INTL function in Excel
Notes :

1. Excel stores dates as serial numbers and is used in calculation by the function. So it’s recommended to use dates as cell reference or using the DATE function instead of giving direct argument to the function. Check the validation of date values if the function returns #VALUE! Error.
2. The function returns #VALUE! Error if any argument in the function is non numeric.
3.  If the start date is out of range, then function returns #NUM! Error.
4. Holidays argument must be given as array reference, not the individual dates separated using commas, the function throws too many arguments error.

Hope this article about How to use the WORKDAY.INTL function in Excel is explanatory. Find more articles on date and time formulas and related Excel functions here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to use the WORKDAY function in Excel : Returns a workday date value after days added to the start_date using the WORKDAYS function in Excel.

How to use the NETWORKDAYS function in Excel : Returns the working days between the two given dates using the NETWORKDAYS function.

Count holidays between dates in Excel : count non working days between the two given dates using the DATEDIF and NETWORKDAYS function in Excel.

Extract days between dates ignoring years in Excel : count the days between the two dates using different functions and mathematical operation in Excel

Count Birth Dates By Month in Excel : count the number of dates lying in a particular month using the SUMPRODUCT and MONTH function in Excel.

SUM price by weekdays in Excel : sum price values corresponding to the number of dates in a particular weekday using the SUMPRODUCT and WEEKDAY function in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.