Holiday Next Business Day formula fix

[AZSue07]

In need of a fix/adjustment to the following formula so if a date falls on a holiday(whether a one day or two day holiday) it changes the date to the next business day with a 08:00 or 09:00 time (EST or EDT dependent). I use a lookup table to list holidays (date only). Current formula is for only if a holiday falls on a Monday. Need it for any day of the week.

My current formula is: (takes into account if daylight savings or standard time)

=IF(OR(WEEKDAY(L5602,2)>5,WORKDAY(L5602,1)+IF(MATCH(L5602,DATE(YEAR(L5602),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(L5602),{1,3,11},7)))<>2,8,9)/24,L5602),IF(WEEKDAY(L5602,2)=1,IF(ISERROR(VLOOKUP(DATE(YEAR(L5602),MONTH(L5602),DAY(L5602)),LkUp!$A$2:$E$29,5,FALSE)),L5602,(INT(L5602)+1.33333)),IF(WEEKDAY(L5602,2)=1,IF(ISERROR(VLOOKUP(DATE(YEAR(L5602),MONTH(L5602),DAY(L5602)),LkUp!$A$2:$E$29,5,FALSE)),L5602,(INT(L5602)+1.375)),L5602)))


L is my created date (mm/dd/yyyy h:mm)

Thank you.

 

[Ron Coderre]

It seems like you could build something around a formula like this:

=WORKDAY(l5602-1,1,LkUp!$A$2:$E$29)

That formula returns the next business day after the referenced-data-minus-one-day, taking holidays into consideration.

Is that something you can work with?

 

[AZSue07]

It seems like you could build something around a formula like this:

=WORKDAY(l5602-1,1,LkUp!$A$2:$E$29)

That formula returns the next business day after the referenced-data-minus-one-day, taking holidays into consideration.

Is that something you can work with?

I am not sure how to apply this formula to produce the result I'm looking for. To further explain what I need:

L is the created date/time. Using a look up table for holidays, I need a formula that will look at L and determine if it falls on a holiday. If not, the date/time in L is also the date/time in P (Holiday Next Business Day). If L is a holiday, I then need it to be converted to the next business day with a time of 09:00 if Eastern Standard Time or 10:00 if Eastern Daylight Savings time appearing in column P. Examples below:


[TABLE="width: 347"]
<tbody>[TR]
[TD="class: xl65, width: 101, bgcolor: transparent"]Created Time
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 169, bgcolor: transparent"]Holiday Next Business Day
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/27/2014 9:54
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/1/2014 9:00
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]11/26/2014 9:54
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]11/26/2014 9:54
[/TD]
[/TR]
</tbody>[/TABLE]