Help with a formmula change in a "flag" parameter!

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 I’d like to make, is this:

If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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

0
Utf
5/19/2010 3:45:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

19 Replies
832 Views

Similar Articles

[PageSpeed] 56

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 I’d like to make, is this:
> 
> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> 
0
Utf
5/19/2010 6:11:01 PM
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 I’d like to make, is this:
> > 
> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> > 
0
Utf
5/19/2010 6:30:01 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> 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 I’d like to make, is this:
>
> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> 

0
Steve
5/20/2010 1:46:18 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> > 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 I’d like to make, is this:
> >
> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> > 
> 
0
Utf
5/20/2010 2:13:01 PM
LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
> 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
>> > 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 I’d like to make, is this:
>> >
>> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
>> >
>> 

0
Steve
5/20/2010 2:20:19 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> >> > 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 I’d like to make, is this:
> >> >
> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> >> >
> >> 
> 
0
Utf
5/20/2010 2:43:01 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
>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" <DannyBoy@discussions.microsoft.com> wrote in message
>> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
>> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
>> >> > 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 I’d like to make, is this:
>> >> >
>> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
>> >> >
>> >>
>> 

0
Steve
5/21/2010 7:04:51 AM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
> >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" <DannyBoy@discussions.microsoft.com> wrote in message
> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> >> >> > 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 I’d like to make, is this:
> >> >> >
> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> >> >> >
> >> >>
> >> 
> 
0
Utf
5/21/2010 3:28:01 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
news:1FD5DDF9-BD91-4540-B399-8B995C610850@microsoft.com...
> 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
>> >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" <DannyBoy@discussions.microsoft.com> wrote in message
>> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
>> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
>> >> >> > 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 I’d like to make, is this:
>> >> >> >
>> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 
>> >> >> > 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
>> >> >> >
>> >> >>
>> >>
>> 

0
Steve
5/24/2010 7:23:26 AM
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" <stunn@sky.com> wrote in message 
news:C6CA0541-0EDB-4582-AD65-6AE8FA2DDA71@microsoft.com...
> 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:1FD5DDF9-BD91-4540-B399-8B995C610850@microsoft.com...
>> 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" <DannyBoy@discussions.microsoft.com> wrote in message
>>> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
>>> >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" <DannyBoy@discussions.microsoft.com> wrote in message
>>> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
>>> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
>>> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
>>> >> >> > 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 I’d like to make, is this:
>>> >> >> >
>>> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 
>>> >> >> > 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
>>> >> >> >
>>> >> >>
>>> >>
>>>
> 

0
Steve
5/24/2010 9:10:30 AM
Hi Steve!

Thank you SO MUCH for your help and clarifications. Sorry for not responding 
sooner, but I've had some computer problems (which are now resolved), and 
over the weekend I didn't have a chance to get online.

I feel that this discussion group is so invaluable in terms of learning. 
Most of my Excel knowledge has come from this group.

Have a great week Steve!

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" <stunn@sky.com> wrote in message 
> news:C6CA0541-0EDB-4582-AD65-6AE8FA2DDA71@microsoft.com...
> > 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> > news:1FD5DDF9-BD91-4540-B399-8B995C610850@microsoft.com...
> >> 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
> >>> >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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
> >>> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> >>> >> >> > 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 I’d like to make, is this:
> >>> >> >> >
> >>> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 
> >>> >> >> > 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
> >>> >> >> >
> >>> >> >>
> >>> >>
> >>>
> > 
> 
0
Utf
5/24/2010 5:48:01 PM
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" <stunn@sky.com> wrote in message 
> news:C6CA0541-0EDB-4582-AD65-6AE8FA2DDA71@microsoft.com...
> > 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> > news:1FD5DDF9-BD91-4540-B399-8B995C610850@microsoft.com...
> >> 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
> >>> >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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
> >>> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
> >>> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
> >>> >> >> > 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 I’d like to make, is this:
> >>> >> >> >
> >>> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 
> >>> >> >> > 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
> >>> >> >> >
> >>> >> >>
> >>> >>
> >>>
> > 
> 
0
Utf
5/24/2010 8:21:01 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
news:175A997B-F747-4F83-94F4-A6B1E38BC401@microsoft.com...
> 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" <stunn@sky.com> wrote in message
>> news:C6CA0541-0EDB-4582-AD65-6AE8FA2DDA71@microsoft.com...
>> > 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> > news:1FD5DDF9-BD91-4540-B399-8B995C610850@microsoft.com...
>> >> 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> >>> news:E9D5EC62-59FF-47DA-A8BD-1083AD432EA6@microsoft.com...
>> >>> >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" <DannyBoy@discussions.microsoft.com> wrote in message
>> >>> >> news:EECA8707-89BC-4D19-AC35-AB1E99B831FD@microsoft.com...
>> >>> >> > 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" <DannyBoy@discussions.microsoft.com> wrote in 
>> >>> >> >> message
>> >>> >> >> news:CF8FFC87-B36A-4B74-91B5-308B2EB2A86E@microsoft.com...
>> >>> >> >> > 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 I’d like to make, is this:
>> >>> >> >> >
>> >>> >> >> > If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d
>> >>> >> >> > 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
>> >>> >> >> >
>> >>> >> >>
>> >>> >>
>> >>>
>> >
>> 

0
Steve
5/25/2010 7:56:09 AM
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 I’d like to make, is this:
> 
> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> 
0
Utf
5/25/2010 9:14:12 PM
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" <DannyBoy@discussions.microsoft.com> wrote in message 
news:FC815F93-9E51-4094-800D-9FAB7B8A2000@microsoft.com...
> 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 I’d like to make, is this:
>>
>> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
>> 

0
Steve
5/26/2010 6:41:55 AM
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" <stunn@sky.com> wrote in message 
news:93EC4F78-B7B5-405E-9B17-30F0E5598B75@microsoft.com...
> 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> news:FC815F93-9E51-4094-800D-9FAB7B8A2000@microsoft.com...
>> 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 I’d like to make, is this:
>>>
>>> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
>>>
> 

0
Steve
5/26/2010 7:25:44 AM
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" <stunn@sky.com> wrote in message 
> news:93EC4F78-B7B5-405E-9B17-30F0E5598B75@microsoft.com...
> > 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> > news:FC815F93-9E51-4094-800D-9FAB7B8A2000@microsoft.com...
> >> 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 I’d like to make, is this:
> >>>
> >>> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> >>>
> > 
> 
0
Utf
5/27/2010 8:17:21 PM
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" <stunn@sky.com> wrote in message 
> > news:93EC4F78-B7B5-405E-9B17-30F0E5598B75@microsoft.com...
> > > 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" <DannyBoy@discussions.microsoft.com> wrote in message 
> > > news:FC815F93-9E51-4094-800D-9FAB7B8A2000@microsoft.com...
> > >> 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 I’d like to make, is this:
> > >>>
> > >>> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
> > >>>
> > > 
> > 
0
Utf
5/27/2010 9:38:28 PM
You're very welcome Dan, glad we got there in the end.


"Danny Boy" <DannyBoy@discussions.microsoft.com> wrote in message 
news:798A79F1-1CAC-40B6-86F7-9F1530ACD197@microsoft.com...
> 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" <stunn@sky.com> wrote in message
>> > news:93EC4F78-B7B5-405E-9B17-30F0E5598B75@microsoft.com...
>> > > 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" <DannyBoy@discussions.microsoft.com> wrote in message
>> > > news:FC815F93-9E51-4094-800D-9FAB7B8A2000@microsoft.com...
>> > >> 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 I’d like to make, is this:
>> > >>>
>> > >>> If Column N is toggled to say “Hudson Sat. & Sun.”,  than I’d 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
>> > >>>
>> > >
>> > 

0
Steve
5/28/2010 7:16:26 AM
Reply:

Similar Artilces:

multicolored follow up flags to color categories won't trun off
Been on for weeks since installing Office 2007. Anyone know how to get rid of the message? Updating the multicolored for follow up flags to color categories How big is your pst-file? Did you had many color-flagged messages in Outlook 2003? A common reason why it could hang is when there are errors in the pst-file. You can check it for errors with scanpst.exe. For details see; http://www.msoutlook.info/question/77 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http...

Flag for Follow-up
Hello: I am having problems with OL's Flag for Follow-up (FFF) feature. I have specified certain messages with FFF, however I do not receive a pop-up reminder. When I go to my Sent Items folder to review certain messages, the message is in red. This probably indicates "past due" or something like that. Any suggestions that I can try to fix this? Any help on this will be appreciated. Thank you. ...

How to flag emails as "spam" all at once in Outlook 2003?
Hi all, Recently I got a lot spam emails... I can only click on each email, and then send each one individually to "spam email" folder in Outlook 2003 separately... How can I select them all and send them all at once into "spam email" folder in one click? There must be some way of doing this: Outlook 2003 should not be so stupid... You can drag and drop them all to the junk folder - but it would be easier to just delete them. Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft Outlook FAQ: http://www.factplac...

How to suppress SW_MAXIMIZE flag?
Hi. The dialog window in my dialog based MFC application has its Maximize button disabled, so, seems there's no way to maximize the window. Anyway, a shortcut with "Maximized" value in the "Run" section can easily run my application with an ugly maximized dialog window. I know, in this case WinMain gets SW_MAXIMIZE as its nCmdShow parameter's value. I tried 2 things, none of which helped: 1) Setting CWinApp::m_nCmdShow to SW_SHOWNORMAL early in InitInstance; 2) Dropping WS_MAXIMIZE window style bit in PreCreateWindow, also tried in OnInitDialog; So, the question ...

Outlook 2007 flags
I exported my Outlook 2007 messages to a PST file, moved the PST file to an external hard drive, then copied the PST file back to my Outlook mailbox. I'm now working in the PST file in my mailbox. I can flag individual messages by clicking in the flag column, but now when I right-click to mark the flagged item complete with a check, that option (to mark completed with a check) is not listed. How can I get this option back? Thank you. ...

creating a flag to identify first, second time etc someone has app
Hi, If I have a dataset showing the same person three times, on three different dates but want to create a flag (as below) which states whether this is the first time this person has appeared in my dataset and so on - how would I be able to do so? e.g Name date of procedure time of procedure flag john smith 15/10/2009 09:30 first procedure john smith 20/11/2009 10:30 second procedure john smith 28/12/2009 10:30 thi...

sort order flag hard to see
Retail Management System - Store Operations This is a fairly minor, picky suggestion. However, it can be annoying. The sort flag (the tiny triangle) used to indicate which column the lookup is sorting by is too hard to see. It is just a shade darker gray than the header where it is located. Could the symbol be set in a different or more contrasting color? My client has a number of employees who have a hard time seeing the flag. Some of them are not the most computer literate people and they need all the help they can get. Thanks, Claude ---------------- This post is a suggesti...

is there a way to add new flag colours in outlook
i would like to add more Coloured flags in outlook, is there a way to do this "alpaj" <alpaj@discussions.microsoft.com> wrote in message news:95E6B55B-E53B-403D-AD00-EF89313CDDC5@microsoft.com... >i would like to add more Coloured flags in outlook, is there a way to do >this You didn't state which version of Outlook that you are using. However, Outlook 2003 Help returned this (remember the F1 key has never broken anything) : Change the message flag color Show All Hide All For messages 1.. Select the messages that you want to cha...

How do you flag messages sent to you that have a Bcc?
How do you know if others are Bcc'd on mail you receive? You don't. That's what makes the 'B' of BCC (Blind). No info is carried in the header of the mail item to say that it's been BCC'd "Dparadise" <Dparadise@discussions.microsoft.com> wrote in message news:CAAD5FA5-570F-481E-81E3-5973B37B3241@microsoft.com... > How do you know if others are Bcc'd on mail you receive? ...

flag a message
I am new to live desktop and do not understand the 4 icons shown on the toolbar; namely a star, spectacles, paper clip and a cloud. In my last hotmail account I could flag a message to note that I should read it later but so far do not see how to do this in windows live mail desktop. The live help line does not seem to give me an answer. Can anyone tell me? Thank you Alt + T, Options, uncheck Use newsgroup community support features. What you are seeing is Rating, Watched, Attachment, Message type. If Flag is not showing, right click on the column headers (it is not the toolba...

CFormView dirty flag
I have a CFormView - derived class that has about 30 Edit controls. Does anyone know what is the best solution to implement dirty flag? It is a database app (uses ADO recordsets). Thanks, Michael This is what I normally do: In your view class, declare a var and a method: class CYourForm : public CFormView { ... protected: BOOL m_bFormIsDirty; afx_msg void OnEnChangeText(); }; Map the EN_CHANGE message for all your text boxes to call the method: BEGIN_MESSAGE_MAP( CMyView, CFormView ) ON_EN_CHANGE(IDC_EDIT1, OnEnChangeText) ON_EN_CHANGE(IDC_EDIT2, OnEnChan...

Epay and processing date flag issue
I had Money's options set to turn off the "enter epays on the processing date" but for some reason the settings got lost. So I now have a transaction with the processing date, which is not how I bank (the MSN Bill Pay will debit on the pay date, not the processing date), and it's messing up my Cash Flow. Does anyone know how to fix an existing transaction, short of cancel and reissue? Thx. ...

Setting the Filter Junk-email flag
I want to enable spam filtering for all users of Exchange 2k3 + IMF in the office. Is there any way to set the Filter Junk-email flag (OWA Options) for all of them? Yes there is. Check details from here: http://www.exchangeinbox.com/articles/008/centraljunk.htm cheers, Alexander Zammit Software Development Consultant Check out, ExchangeInbox.com the MS Exchange Resource site at http://www.exchangeinbox.com/ "Val" <Val@discussions.microsoft.com> wrote in message news:5EF7E60D-39DB-4564-A0E2-6F0A52144C4C@microsoft.com... >I want to enable spam filtering for all users o...

Corruption Flag
What is the Microsoft Access corruption flag? How can I find it? How can I find the value of it, "set or not set" with VBA (An external database, of course) Any info appreciated..... Thanks DS -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1 I've only seen it in memo fields. In my experience, it marks the field with # as the first character, but that is also an allowable character. As a matter of fact, if you search for it, you will only find the allowable characters, not the flag. The last time I've see...

Different application Icon based on compiler flag
Hello, I have an application that can be compiled as a Player or as an Editor. The selection is based on wether a compiler flag is defined during compilation. Is it possible to have a different application icon (not only in the titlebar, that is no problem) that shows up when you see the .exe in Windows Explorer. Something like: #if D_EDITOR_MODE use editor icons 16x16 and 32x32 #else use player icons 16x16 and 32x32 #endif Any suggestions ? Thanks, Yes it's possible. I believe that the icon shown by explorer is just the first one it finds in the exes' bound resources, or at...

Error flag in spreadsheet
I keep getting those little error flags in my spreadsheet even though it works ok and then I have to `ignore error` which takes ages to sort, is thre a way to stop the `error message` appearing. thanks S S, tools, options, error checking, uncheck enable background error checking -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "S S" <yamxs750@blueyonder.co.uk> wrote in message news:GjAQf.141053$DM.2840@fe3.news.bl...

Comparing two columns of data and flag any duplicates in the third column
Hi, Can any one help me with a Macro which will compare Data in column �A with Data in column �B� and if any duplicates are found in column �B enter the word �Duplicate� in the corresponding cell in column �C�. I no duplicate is found enter the word �OK� in the corresponding cell i column �C� e.g, 1234456 123456 Duplicate 1456789 198433 OK any help who be greatly appreciated Many Thanks Ro -- Message posted from http://www.ExcelForum.com Hi Robert! You'll probably want a lot more than this so take a trip to: Chip Pearson: http://www.cp...

Creating flags for certain fields in working Form
Hello, What is the best way to add a flag for specific fields? For example, Field A has a value and depending on the value, the flag could change be a choice such as Yes Or No or even changing the color of that specific cell in the table. Any advice much appreciated in advance. -- Best regards, Donald Ramer donaldramer@comcast.net You and I may not mean the same thing by "flag". If you want formatting changes depending of field value, take a look at "Conditional Formatting". -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://m...

Flagged Items are missing after Snow Leapord upgrade
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi, <br><br>I just upgraded one of my users Mac Book Pros to Snow Leapord and now his Flagged emails are gone. Not the emails themselves but all the flagged items. When I log into Webmail, I see all the flagged items but not in Entourage. Anybody have any ideas on whats wrong or how I can recover the flagged items? Any help would be great. Thanks in advance. <br><br>Robert ...

Help with a formmula change in a "flag" parameter!
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 I’d like to make, is this: If Column N is toggled to say “Hudson ...

Outlook Flags
When I turn a custom flag on an item, e.g. email, specify some due date, the default remainder (by default unchecked) is set for "Saturday, January 01, 4501". How can I change this strange date to be *automatically* something more convenient, e.g. the due date? Version of outlook? Have you tried office diagnotics? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange...

Follow Up Flag #2
When I set a follow up flag in Outlook 2000 it inserts a default time of 18:00. Is there a way to alter this default or do I have to change the time manually for every flag I set? ...

RE: Replacing the standard, waving-flag Windows logo icon
I'm sorry but I have to re-post this again as, unfortunately, was unsuccessful so far in resolving this issue and there's some time pressure building up. My application is SDI and uses AfxRegisterWndClass() in both, CMainFrame::PreCreateWindow() and CChildView::PreCreateWindow(), what is done automatically by the MFC wizard. I slightly change their default implementation so that, instead of default cursor and icon, customized ones could be used : // ----- below is from CChildView::PreCreateWindow() cs.lpszClass = AfxRegisterWndClass(CS_HREDRAW | CS_VREDRAW | CS_DBLCLKS, ::LoadCur...

Storing Email when sending and flagging for followup
Hi, I send an email to a particular email address on a semi regular basis, which I then have to chase up at a later stage to find out the progress on a piece of work. What I would like to do, is automatically save the sent email in a particular folder on my laptop and set some sort of reminder automatically to contact them again in 7days time.. I have no idea if this can be done or how to do it if it can, can anyone help me please?? Regards John John Venables <JohnVenables@discussions.microsoft.com> wrote: > Hi, I send an email to a particular email address on a semi regul...

How do I find out if I can use some clip art for a school flag?
I am wanting to get permission to use some clip art for our school flag and wondering how to go about finding out what I need to do... I am still using the publisher 97 program. Basically 8)... 1.2 License Grant for Media Elements. The Software may include certain photographs, clip art, shapes, animations, sounds, music and video clips that are identified in the Software for your use (together "Media Elements"). You may copy and modify the Media Elements, and license, display and distribute them, along with your modifications as part of your software products and services...