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.

Excel Workday Function

Workday Function

Workday function returns a date, N working days in future or past and it can be used to add or subtract workdays to a given date. It calculates workday by excluding the start date. Workday function is used to calculate due dates, delivery dates, and other dates that should exclude non-working days.

Syntax

=workday(start date, days, holidays)

Arguments

• Start date- REQUIRED. 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.
• Holidays. 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.

*Date – be it a start date or a date in the holiday list- should be entered using Date Function or as result of other formula or functions. Problem can occur if date is entered as TEXT (#VALUE! Error occurs).

Points to Keep in Mind:

• If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.
• If days is not an integer, it is truncated.

By default, workday function excludes weekends as Saturday & Sunday for fetching the workday in future or past. To customize which day(s) of the week is/ are considered as weekend, use Excel WORKDAY.INTL Function.