Adding days to a date and allowing for holidays

[Neil_R]

I have a formula that will happily add a set number of days to a date and if the outcome is a weekend it will then move to the next working day. I need to add another variable to account for any extra holidays the date may land on. Its counting calendar days so the only relevant date is the end date, if it falls on a weekend or another holiday it needs to move to the next working day. What I have so far is this:

=IF(WEEKDAY(E4+10, 2)>5, (IF(WEEKDAY(E4+10, 2)=6, E4+12, E4+11)), E4+10)

E4 is the date to start counting from and this works fine but then I cant find a way of including a list of dates to move the result on again if it lands on one of these dates. I could use extra columns but am trying to do it in one formula.

Any ideas are welcomed.

 

[Eric W]

Check out the WORKDAY function:

Book1
	E	F	G
1			holidays
2			12/25/2019
3	12/23/2019	1/3/2020	1/1/2020
Sheet5

Cell Formulas
Range	Formula
F3	F3	=WORKDAY(E3,7,G2:G3)

The WORKDAY.INTL offers even more options.

 

[Neil_R]

Check out the WORKDAY function:

Book1
	E	F	G
1			holidays
2			12/25/2019
3	12/23/2019	1/3/2020	1/1/2020
Sheet5

Cell Formulas
Range	Formula
F3	F3	=WORKDAY(E3,7,G2:G3)

The WORKDAY.INTL offers even more options.

The issue with this is that it then excludes the weekend days from the count, I need to count calendar days and only move the end date if its a weekend or other holiday. I don't think this then works or am I missing something?

 

[Eric W]

True, the WORKDAY formula as built will exclude all weekends/holidays within the range, but you can adjust it like this:

Book1
	E	F	G
1			holidays
2			12/25/2019
3	12/23/2019	12/30/2019	1/1/2020
Sheet5

Cell Formulas
Range	Formula
F3	F3	=WORKDAY(E3+5,1,G2:G3)

If you want to add 6 days to the date in E3, and then skip to the next workday if that's not a workday, then add 5 to the date, and use WORKDAY to increment by 1 more, and it will check for the weekend/holidays for the last day only.