Get Nthworking day before and after a given date with custom weekend days.
The Excel WORKDAYS.INTL returns a working day before and after a date, excluding custom Weekend days. Example; to find a working day 2 days after a date (01-11-2018- cell B7) excluding Friday and Saturday, use =WORKDAY.INTL(B7,2,7), that returns ‘05-11-2018’.
Moreover, an optional argument (holidays) can also exclude holidays, vacation time, and so on.
= WORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
start_date – The Start date.
weekend [optional] – Setting custom weekend days
holidays [optional] – A list of additional days to be excluded apart from weekends.
Working date as per arguments.
- The WORKDAY.INTL function returns the nearest working day after excluding weekend days (Saturdays and Sundays as default).
- The result includes the start_date as the first date for calculating return value. The second argument for the WORKDAY function can be positive or negative for calculating future or past dates respectively.
- The WORKDAY.INTL function was introduced with the Excel 2010 providing a higher degree of freedom compared to the WORKDAY formula.
- As with all dates in Excel, what you end up with is a serial number, but the Date formatting displays the date in a readable fashion.
- The weekend is an optional argument to select customize weekend number or string that specifies when a weekend occurs in a week to excel.
Weekend number value indicates the following weekend days:
|1 or omitted||Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.|
|2||Numbers 1 (Monday) through 7 (Sunday).|
|3||Numbers 0 (Monday) through 6 (Sunday).|
|11||Numbers 1 (Monday) through 7 (Sunday).|
|12||Numbers 1 (Tuesday) through 7 (Monday).|
|13||Numbers 1 (Wednesday) through 7 (Tuesday).|
|14||Numbers 1 (Thursday) through 7 (Wednesday).|
|15||Numbers 1 (Friday) through 7 (Thursday).|
|16||Numbers 1 (Saturday) through 7 (Friday).|
|17||Numbers 1 (Sunday) through 7 (Saturday).|
To find previous day when you do work use WORKDAYS.INTL function. for example, =WORKDAYS.INTL(10 June 2019,-8) it return 30 May 2019. it means to find last day work we will write date in negative form (-8).
Same you can apply for next work day only remove negative sign from days and if there are holiday you can exclude the days by putting the days in holiday parameter.