Triggering a Flag after 10 days that excludes weekends and holiday

[Danny Boy]

The formula below works just fine. It triggers the flag "Return Referral Form
to Referent if more than 10 days elapse per the dictates of the formula below
(column H represents the date in which a referral was made, and column K
represents the date in which someone enrolled):

=IF(H11="","",IF(K11<>"","",IF(T11="Sent","Client Failed to Enroll on
Time",IF(AND(K11="",TODAY()>H11+17),"Return Referral Form to Referent",""))))

However I would like to modify the above formula so that weekends and major
holidays are not included in determining the elapsed 10 days. Thus only
business days should be considered in triggering the 10 day flag.

The holidays my agency considers as "major holidays" are: NY Day, Martin
Luther King Day, Presidents Day, Memorial Day, Independence Day, Labor Day,
Thanksgiving Day & the day after, Veterans Day, and Christmas Day.

 

[Stefi]

=IF(H11="","",IF(K11<>"","",IF(T11="Sent","Client Failed to Enroll on
Time",IF(AND(K11="",TODAY()>WORKDAY(H11,10,Z1:Z10)),"Return Referral Form to
Referent",""))))

Enter holiday dates in Z1:Z10!

Make sure that Analisys Toolpak is turned on!


Regards,
Stefi

„Danny Boy†ezt írta:

 

[Danny Boy]

Thank you Stefi. That worked. I take it you changed part of the formula from
(H11+10) to (H11,10) to take into account the modifications you made. I also
presume (tell me if I'm wrong) that when I enter the holildays into Column Z,
I will have to re-do this every year (as the dates for all except NY Day,
Independence Day and Christmas Day change yearly).

Thanks much!

Dan

 

[Stefi]

Thank you Stefi. That worked. I take it you changed part of the formula from

(H11+10) to (H11,10) to take into account the modifications you made.

Not exactly! I changed
H11+10 to
WORKDAY(H11,10,Z1:Z10)
That's a new function with its own arguments!

I also

presume (tell me if I'm wrong) that when I enter the holildays into Column Z,
I will have to re-do this every year (as the dates for all except NY Day,
Independence Day and Christmas Day change yearly).

Have a look at Chip Pearson's site:

http://www.cpearson.com/excel/holidays.htm

It contains solutions for some floating holidays. If you can find a formula
for determining the date of a specific holiday, enter this formula in the
Holidays range, if not, then yes, you have to update Holidays range each year.

Regards,
Stefi