Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Triggering a Flag after 10 days that excludes weekends and holiday
=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: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Triggering a Flag after 10 days that excludes weekends and hol
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" wrote: =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: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Triggering a Flag after 10 days that excludes weekends and hol
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Employee days worked (-Holidays, -weekends, Snow Days, etc) | Excel Discussion (Misc queries) | |||
Counting half days holiday and sickness in excel | New Users to Excel | |||
Auto holiday days populate on other sheets | Excel Worksheet Functions | |||
i need the date to flag up red if it goes over 30 days from the d. | Excel Discussion (Misc queries) | |||
How do I use the IF formula to flag me 21 days after the date? | Excel Worksheet Functions |