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

Hi,
=if(N1="Hudson Sat. & Sun",your new formula,your first formula)

"Danny Boy" wrote:

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

Not exactly clear on your suggestion Eduardo (in terms of how to change the
flag deadline date from 10 days to 45 days if N4 is toggled to "Hudson Sat. &
Sun."). Also not sure where to insert this additional piece of formula based
upon your suggestion.

Thanks, Dan

"Eduardo" wrote:

Hi,
=if(N1="Hudson Sat. & Sun",your new formula,your first formula)

"Danny Boy" wrote:

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

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

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

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

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

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

Hi Steve!

When most students were given 10 days to enroll, this DID NOT COUNT weekends
or holidays, and as such, the "Return Referral Form to Referent" was
flagging at approximately 14 days (a few days more if there was a holiday
involved). This will continue to work fine for all NON HUDSON students.

However, when we began seeing students in Hudson, this school system gives
them up to 45 days to enroll, but DOES COUNT weekends and holidays.

Given the current forumla, these Hudson students are flagging at
approximately day 63 as you said, becasuse $AA$4:$AA$12 is the piece of the
current formula that gives students extra enrollment time in order to not
penalize them when a weekend or holiday occurs.

In addition, Hudson students have to complete their class within 90 days of
their referral, so this could be a problem if the enrollement deadline flag
occurs at day 63. It would be difficult to enroll and complete with only 27
days left to go.

I think the fix will be this.........

Any student identified as being in a Hudson class (Column N) should flag
"Return Referral Form to Referent" at day 45 (not counting weekends or
holidays). With all other students, we can leave the 10 days to enroll as is,
as well as continuing to factor in weekends and holidays as we had been doing
up to this point. Does that make sense?

So I suspect that the formula will have to ignore the holiday allowances
($AA$4:$AA$12) when a student is identified in Column N as taking a Hudson
class.

Thanks again so much for your help Steve!

Dan

"Danny Boy" wrote:

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

We're nearly there then, this should do it:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Danny Boy" wrote in message
...
Hi Steve!

When most students were given 10 days to enroll, this DID NOT COUNT
weekends
or holidays, and as such, the "Return Referral Form to Referent" was
flagging at approximately 14 days (a few days more if there was a holiday
involved). This will continue to work fine for all NON HUDSON students.

However, when we began seeing students in Hudson, this school system gives
them up to 45 days to enroll, but DOES COUNT weekends and holidays.

Given the current forumla, these Hudson students are flagging at
approximately day 63 as you said, becasuse $AA$4:$AA$12 is the piece of
the
current formula that gives students extra enrollment time in order to not
penalize them when a weekend or holiday occurs.

In addition, Hudson students have to complete their class within 90 days
of
their referral, so this could be a problem if the enrollement deadline
flag
occurs at day 63. It would be difficult to enroll and complete with only
27
days left to go.

I think the fix will be this.........

Any student identified as being in a Hudson class (Column N) should flag
"Return Referral Form to Referent" at day 45 (not counting weekends or
holidays). With all other students, we can leave the 10 days to enroll as
is,
as well as continuing to factor in weekends and holidays as we had been
doing
up to this point. Does that make sense?

So I suspect that the formula will have to ignore the holiday allowances
($AA$4:$AA$12) when a student is identified in Column N as taking a Hudson
class.

Thanks again so much for your help Steve!

Dan

"Danny Boy" wrote:

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

Scratch that, try this:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))*(LEFT(N4,6)< "Hudson")+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Steve Dunn" wrote in message
...
We're nearly there then, this should do it:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Danny Boy" wrote in message
...
Hi Steve!

When most students were given 10 days to enroll, this DID NOT COUNT
weekends
or holidays, and as such, the "Return Referral Form to Referent" was
flagging at approximately 14 days (a few days more if there was a holiday
involved). This will continue to work fine for all NON HUDSON students.

However, when we began seeing students in Hudson, this school system
gives
them up to 45 days to enroll, but DOES COUNT weekends and holidays.

Given the current forumla, these Hudson students are flagging at
approximately day 63 as you said, becasuse $AA$4:$AA$12 is the piece of
the
current formula that gives students extra enrollment time in order to not
penalize them when a weekend or holiday occurs.

In addition, Hudson students have to complete their class within 90 days
of
their referral, so this could be a problem if the enrollement deadline
flag
occurs at day 63. It would be difficult to enroll and complete with only
27
days left to go.

I think the fix will be this.........

Any student identified as being in a Hudson class (Column N) should flag
"Return Referral Form to Referent" at day 45 (not counting weekends or
holidays). With all other students, we can leave the 10 days to enroll as
is,
as well as continuing to factor in weekends and holidays as we had been
doing
up to this point. Does that make sense?

So I suspect that the formula will have to ignore the holiday allowances
($AA$4:$AA$12) when a student is identified in Column N as taking a
Hudson
class.

Thanks again so much for your help Steve!

Dan

"Danny Boy" wrote:

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

Hey Steve!

Just got in to work and off to a few meetings (ah the life of an
administrator). When the dust settles today I will plug in the formula and
see how it works. I'll get back to you ASAP. You've been extremely helpful my
friend. This is fun. I love a challenge!

Dan

"Steve Dunn" wrote:

Scratch that, try this:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))*(LEFT(N4,6)< "Hudson")+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Steve Dunn" wrote in message
...
We're nearly there then, this should do it:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Danny Boy" wrote in message
...
Hi Steve!

When most students were given 10 days to enroll, this DID NOT COUNT
weekends
or holidays, and as such, the "Return Referral Form to Referent" was
flagging at approximately 14 days (a few days more if there was a holiday
involved). This will continue to work fine for all NON HUDSON students.

However, when we began seeing students in Hudson, this school system
gives
them up to 45 days to enroll, but DOES COUNT weekends and holidays.

Given the current forumla, these Hudson students are flagging at
approximately day 63 as you said, becasuse $AA$4:$AA$12 is the piece of
the
current formula that gives students extra enrollment time in order to not
penalize them when a weekend or holiday occurs.

In addition, Hudson students have to complete their class within 90 days
of
their referral, so this could be a problem if the enrollement deadline
flag
occurs at day 63. It would be difficult to enroll and complete with only
27
days left to go.

I think the fix will be this.........

Any student identified as being in a Hudson class (Column N) should flag
"Return Referral Form to Referent" at day 45 (not counting weekends or
holidays). With all other students, we can leave the 10 days to enroll as
is,
as well as continuing to factor in weekends and holidays as we had been
doing
up to this point. Does that make sense?

So I suspect that the formula will have to ignore the holiday allowances
($AA$4:$AA$12) when a student is identified in Column N as taking a
Hudson
class.

Thanks again so much for your help Steve!

Dan

"Danny Boy" wrote:

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

It seems to be working perfectly Steve!

Thank you so much!!!!!!!!!!!!!!!!!

Dan

"Danny Boy" wrote:

Hey Steve!

Just got in to work and off to a few meetings (ah the life of an
administrator). When the dust settles today I will plug in the formula and
see how it works. I'll get back to you ASAP. You've been extremely helpful my
friend. This is fun. I love a challenge!

Dan

"Steve Dunn" wrote:

Scratch that, try this:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))*(LEFT(N4,6)< "Hudson")+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Steve Dunn" wrote in message
...
We're nearly there then, this should do it:

=IF((H4="")+(K4<""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((TODAY()WORKDAY(H4,10,AA4:AA12))+
(TODAY()H4+45)*(LEFT(N4,6)="Hudson"),
"Return Referral Form to Referent","")))



"Danny Boy" wrote in message
...
Hi Steve!

When most students were given 10 days to enroll, this DID NOT COUNT
weekends
or holidays, and as such, the "Return Referral Form to Referent" was
flagging at approximately 14 days (a few days more if there was a holiday
involved). This will continue to work fine for all NON HUDSON students.

However, when we began seeing students in Hudson, this school system
gives
them up to 45 days to enroll, but DOES COUNT weekends and holidays.

Given the current forumla, these Hudson students are flagging at
approximately day 63 as you said, becasuse $AA$4:$AA$12 is the piece of
the
current formula that gives students extra enrollment time in order to not
penalize them when a weekend or holiday occurs.

In addition, Hudson students have to complete their class within 90 days
of
their referral, so this could be a problem if the enrollement deadline
flag
occurs at day 63. It would be difficult to enroll and complete with only
27
days left to go.

I think the fix will be this.........

Any student identified as being in a Hudson class (Column N) should flag
"Return Referral Form to Referent" at day 45 (not counting weekends or
holidays). With all other students, we can leave the 10 days to enroll as
is,
as well as continuing to factor in weekends and holidays as we had been
doing
up to this point. Does that make sense?

So I suspect that the formula will have to ignore the holiday allowances
($AA$4:$AA$12) when a student is identified in Column N as taking a
Hudson
class.

Thanks again so much for your help Steve!

Dan

"Danny Boy" wrote:

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