View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Danny boy Danny boy is offline
external usenet poster
 
Posts: 102
Default Triggering a Flag after 10 days that excludes weekends and holiday

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" a NY Day, Martin
Luther King Day, Presidents Day, Memorial Day, Independence Day, Labor Day,
Thanksgiving Day & the day after, Veterans Day, and Christmas Day.