Help with a formmula change in a "flag" parameter!
Hi Dan,
surely, looking at it that way, a 45 day student has 63 days to enroll if
weekends are taken into account?
Is the actual requirement 49 days overall (7 weeks)? In which case the
figure 45 should in fact be 35 (7 * 5-day weeks). Which would then make the
formula:
=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF(TODAY()WORKDAY(H4,10+25*(LEFT(N4,6)="Hudson"), AA4:AA12),
"Return Referral Form to Referent","")))
Does that now work the way you expected?
Steve D.
"Danny Boy" wrote in message
...
Meant to ask you Steve if you had another suggestion for replacing ()TODAY
in
the formula?
My goal is that the "Return Referral Form to Referent" flag should
populate
10 days after the refferal was made (for all but Hudson folk who have 45
days
as you know).
As you also know, this isn't a pure 10 or 45 days, given that this time
frame excludes all weekends and holidays (which are input into the formula
as
reflected with AA4:AA12).
Typically a 10 day student has 14 days to enroll (taking into account
weekends without a holiday), and a 45 day student has 49 days to enroll
(taking into account weekends without a holiday). We don't consider
weekends
and holidays as part of their 10 or 45 day enrollment deadline. So in
essence
they do get a bit more than 10 or 45 days to enroll.
I place the date of referral in Column H, and once a student enrolls, I
place their enrollment date in Column K. If I don't input an enrollment
date
into Column K, and if the deadline to enroll date passes, then the "Return
Referral Form to Referent" flag should populate in Column R.
Hope this adds clarity to my goal...................Hope you are well!
Best,
Dan
"Steve Dunn" wrote:
BTW, I've just spotted a little bit of redundancy (subconciously I
noticed
it first time through, but just couldn't pin down what was bugging me
while
I was looking at the bigger picture) - you don't need to check for K4=""
in
the third IF because any other possibility was dealt with by the first.
=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF(TODAY()WORKDAY(H4,10+35*(LEFT(N4,6)="Hudson"), AA4:AA12), "Return
Referral Form to Referent","")))
will achieve the same.
"Steve Dunn" wrote in message
...
Hi Dan, I've been called many things so Ron didn't really bother me,
and I
have a lot of respect for Ron de Bruin (whom, I presume, you were
thinking
of).
The only part of the formula which deals with the number of workdays
added
is:
10+35*(LEFT(N4,6)="Hudson")
which can only result in 10 or 45.
A comparison such as (a=b) when combined with other values (in an Excel
formula) results in either 0 or 1, representing FALSE or TRUE. So the
number of days added would either be 10+35*0 = 10 when the first 6
characters of N4 are not "Hudson", or 10+35*1 = 45 when they are. There
are no other possibilities here.
If your holiday list AA4:AA12 was long enough I would have said the
problem lay in it's content, but, since it isn't, the only other thing
I
can think of is: Do you really want TODAY() in your formula, or should
that refer to some other date? Remember that TODAY() will update every
time the workbook is opened/recalculated.
HTH
Steve D.
"Danny Boy" wrote in message
...
Sorry about the name thing Steve. I don't mind Fred however lol.
Thanks for the formula update. The only thing I notice however is that
the
"Return Referral Form to Referent" flag for all Hudson classes is now
occuring after day 63, as opposed to after day 45. The triggering of
this
flag for classes other than Hudson is working correctly however.
Suggestion
for what I need to adjust so that all Hudson classes (identified in
Column N)
trigger the "Return Referral Form to Referent" after day 45. Again
thank
you
so much for your help!
Dan or Fred
"Steve Dunn" wrote:
Ok Fred, here you go:
=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on
Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(LEFT(N4,6)=" Hudson"),
AA4:AA12)), "Return Referral Form to Referent","")))
"Danny Boy" wrote in message
...
I think I'm dumb Ron (lol). For some reason I'm having difficulty
inserting
that change and getting it to work. If you wouldn't mind, could you
spell
the
whole formula out (using the change you just gave me) so I can
merely
copy
and paste?
MUCH THANKS!!!!
Dan
"Steve Dunn" wrote:
LEFT(N4,6)="Hudson"
instead of
N4="Hudson Sat. & Sun."
"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!
One final question however..............Usually our class is
"Hudson
Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or
"Hudson
Sun".
Is
there a way of using a wild card in the formula, so that any
class
in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun)
can
trigger
the 45 day flag as you set up?
Thanks so much!
Dan
"Steve Dunn" wrote:
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
|