Help with a formmula change in a "flag" parameter!
you could use:
=IF(H4="","",IF(K4<"","",IF(S4="Sent","Client Failed to Enroll on Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))
but this (imho) is a little tidier:
=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))
"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a flag
for
me (in Column R) that lets me know one of two things: €śClient Failed to
Enroll on Time€ť, and €śReturn Referral Form to Referent€ť. Here is that
formula:
=IF(H4="","",IF(K4<"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral Form
to
Referent",""))))
The one change (addition) that Id like to make, is this:
If Column N is toggled to say €śHudson Sat. & Sun.€ť, than Id like the
following portion of the formula above to change:
TODAY()WORKDAY(H4,45,AA4:AA12))
Please note, that the only change in the string of formula above is the
reference to when the flag €śReturn Referral Form to Referent€ť is
triggered.
In the original formula, this flag triggers in 10 days (excluding holidays
and weekends) from the date of the referral (which we identify in column
H).
If Column N is toggled to say €śHudson Sat. & Sun.€ť, than I want to EXTEND
the
length of time in which this flag would trigger (making it trigger at 45
days-as opposed to 10). The 45 days would continue to exclude weekends and
holidays as was true with the 10 day parameter.
Thanks in advance for any help!
Dan
|