Excel WORKDAY.INTL Function

Excel WORKDAY.INTL Function

A built-in excel function that is categorized as date/ time function, WORKDAY.INTL can be used as a worksheet function. It is introduced in MS Excel 2010 version. The function takes a date as input and returns a workday in future or past depending on the offset value you provide. The only different between Workday and Workday.INTL is that the Workday.INTL allows us to customize the weekend days. It is used to calculate completion dates, ship dates, etc. that take into account non-working days.

Purpose

To get N workday in past or future.

Return Value

Next or previous working date based on inputs.

Return Type

The WORKDAY.INTL function returns a serial date value representing the number of days since January 1, 1900

Syntax

Workday.INTL(start_date, days, weekend, holidays)

Arguments

• Start_date. date from which working days needs to count
• Workday function excludes the start date while fetching the workday.
• Days. +ve for future working date and –ve for past working date, a zero value yields the start_date. If it is supplied as a decimal number, it is truncated to the integer.
• Weekend. Optional. It can be specified which day or days are weekend days. It can either be a number or a string as

Number	Weekend days
1 or omitted	Saturday, Sunday
2	Sunday, Monday
3	Monday, Tuesday
4	Tuesday, Wednesday
5	Wednesday, Thursday
6	Thursday, Friday
7	Friday, Saturday
11	Sunday only
12	Monday only
13	Tuesday only
14	Wednesday only
15	Thursday only
16	Friday only
17	Saturday only

What is Weekend String Here?

It is a series of seven “0s” or seven “1s” that represent the seven days of the week, beginning with Monday. 1 represents a non-working day and 0 is used to represent a workday.

Example:0000011 – Saturday & Sunday are weekend days

1000001- Monday and Sunday are weekend days

Holiday. It is optional. A list of date or an array constant of the serial numbers representing the dates that should be considered as non-working days. Excel only cares about holiday dates, not holiday names.

Points to Keep in Mind:

·         If start-Date /a date in holiday list is out of the current database value, WORKDAY.INTL returns #NUM! error.

·         If start_date plus day-offset yields an invalid date, WORKDAY.INTL returns #NUM! error.

·         If weekend string is of invalid length or contains invalid characters, , WORKDAY.INTL returns #VALUE! error. (string length is 7 containing 0 and 1)

·         Make the list of holiday absolute reference so that it won’t change when you paste the formula to other cells. Example. If your holidays are stored in the range G3:G6, it is best to enter the range as $G$3:$G$6.

Types of Errors That Workday and Workday.INTL Returns:

·         #NUM! error occurs if either:

A combination of the start_date and days arguments results in an invalid date, or

Weekend argument in the WORKDAY.INTL function is invalid.

·         #VALUE! error occurs if either:

start_date or any value in holidays is not a valid date, or

Days argument is non-numeric.