A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 04:45 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default 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

  #2  
Old May 19th, 2010, 07:11 PM posted to microsoft.public.excel.worksheet.functions
eduardo
external usenet poster
 
Posts: 2,131
Default Help with a formmula change in a "flag" parameter!

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

  #3  
Old May 19th, 2010, 07:30 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default Help with a formmula change in a "flag" parameter!

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

  #4  
Old May 20th, 2010, 02:46 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help with a formmula change in a "flag" parameter!

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a flag
for
me (in Column R) that lets me know one of two things: “Client Failed to
Enroll on Time”, and “Return Referral Form to Referent”. Here is that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral Form
to
Referent",""))))

The one change (addition) that 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


  #5  
Old May 20th, 2010, 03:13 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default Help with a formmula change in a "flag" parameter!

Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson Sat. &
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson Sun". Is
there a way of using a wild card in the formula, so that any class in Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a flag
for
me (in Column R) that lets me know one of two things: “Client Failed to
Enroll on Time”, and “Return Referral Form to Referent”. Here is that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral Form
to
Referent",""))))

The one change (addition) that 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


  #6  
Old May 20th, 2010, 03:20 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help with a formmula change in a "flag" parameter!

LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson Sun".
Is
there a way of using a wild card in the formula, so that any class in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can
trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a flag
for
me (in Column R) that lets me know one of two things: “Client Failed to
Enroll on Time”, and “Return Referral Form to Referent”. Here is that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral
Form
to
Referent",""))))

The one change (addition) that 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



  #7  
Old May 20th, 2010, 03:43 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default Help with a formmula change in a "flag" parameter!

I think I'm dumb Ron (lol). For some reason I'm having difficulty inserting
that change and getting it to work. If you wouldn't mind, could you spell the
whole formula out (using the change you just gave me) so I can merely copy
and paste?

MUCH THANKS!!!!

Dan

"Steve Dunn" wrote:

LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson Sun".
Is
there a way of using a wild card in the formula, so that any class in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can
trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a flag
for
me (in Column R) that lets me know one of two things: “Client Failed to
Enroll on Time”, and “Return Referral Form to Referent”. Here is that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral
Form
to
Referent",""))))

The one change (addition) that 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



  #8  
Old May 21st, 2010, 08:04 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help with a formmula change in a "flag" parameter!

Ok Fred, here you go:

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



"Danny Boy" wrote in message
...
I think I'm dumb Ron (lol). For some reason I'm having difficulty inserting
that change and getting it to work. If you wouldn't mind, could you spell
the
whole formula out (using the change you just gave me) so I can merely copy
and paste?

MUCH THANKS!!!!

Dan

"Steve Dunn" wrote:

LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson
Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson
Sun".
Is
there a way of using a wild card in the formula, so that any class in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can
trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on
Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a
flag
for
me (in Column R) that lets me know one of two things: “Client Failed
to
Enroll on Time”, and “Return Referral Form to Referent”. Here is
that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral
Form
to
Referent",""))))

The one change (addition) that 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




  #9  
Old May 21st, 2010, 04:28 PM posted to microsoft.public.excel.worksheet.functions
Danny boy
external usenet poster
 
Posts: 106
Default Help with a formmula change in a "flag" parameter!

Sorry about the name thing Steve. I don't mind Fred however lol.

Thanks for the formula update. The only thing I notice however is that the
"Return Referral Form to Referent" flag for all Hudson classes is now
occuring after day 63, as opposed to after day 45. The triggering of this
flag for classes other than Hudson is working correctly however. Suggestion
for what I need to adjust so that all Hudson classes (identified in Column N)
trigger the "Return Referral Form to Referent" after day 45. Again thank you
so much for your help!

Dan or Fred

"Steve Dunn" wrote:

Ok Fred, here you go:

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



"Danny Boy" wrote in message
...
I think I'm dumb Ron (lol). For some reason I'm having difficulty inserting
that change and getting it to work. If you wouldn't mind, could you spell
the
whole formula out (using the change you just gave me) so I can merely copy
and paste?

MUCH THANKS!!!!

Dan

"Steve Dunn" wrote:

LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson
Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson
Sun".
Is
there a way of using a wild card in the formula, so that any class in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can
trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on
Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates a
flag
for
me (in Column R) that lets me know one of two things: “Client Failed
to
Enroll on Time”, and “Return Referral Form to Referent”. Here is
that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return Referral
Form
to
Referent",""))))

The one change (addition) that 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




  #10  
Old May 24th, 2010, 08:23 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Help with a formmula change in a "flag" parameter!

Hi Dan, I've been called many things so Ron didn't really bother me, and I
have a lot of respect for Ron de Bruin (whom, I presume, you were thinking
of).

The only part of the formula which deals with the number of workdays added
is:

10+35*(LEFT(N4,6)="Hudson")

which can only result in 10 or 45.

A comparison such as (a=b) when combined with other values (in an Excel
formula) results in either 0 or 1, representing FALSE or TRUE. So the
number of days added would either be 10+35*0 = 10 when the first 6
characters of N4 are not "Hudson", or 10+35*1 = 45 when they are. There are
no other possibilities here.

If your holiday list AA4:AA12 was long enough I would have said the problem
lay in it's content, but, since it isn't, the only other thing I can think
of is: Do you really want TODAY() in your formula, or should that refer to
some other date? Remember that TODAY() will update every time the workbook
is opened/recalculated.

HTH
Steve D.



"Danny Boy" wrote in message
...
Sorry about the name thing Steve. I don't mind Fred however lol.

Thanks for the formula update. The only thing I notice however is that the
"Return Referral Form to Referent" flag for all Hudson classes is now
occuring after day 63, as opposed to after day 45. The triggering of this
flag for classes other than Hudson is working correctly however.
Suggestion
for what I need to adjust so that all Hudson classes (identified in Column
N)
trigger the "Return Referral Form to Referent" after day 45. Again thank
you
so much for your help!

Dan or Fred

"Steve Dunn" wrote:

Ok Fred, here you go:

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



"Danny Boy" wrote in message
...
I think I'm dumb Ron (lol). For some reason I'm having difficulty
inserting
that change and getting it to work. If you wouldn't mind, could you
spell
the
whole formula out (using the change you just gave me) so I can merely
copy
and paste?

MUCH THANKS!!!!

Dan

"Steve Dunn" wrote:

LEFT(N4,6)="Hudson"

instead of

N4="Hudson Sat. & Sun."



"Danny Boy" wrote in message
...
Your formula worked perfectly Ron!

One final question however..............Usually our class is "Hudson
Sat.
&
Sun.", however, sometimes our classes are "Hudson Sat", or "Hudson
Sun".
Is
there a way of using a wild card in the formula, so that any class
in
Column
N which is a Hudson class (regardless of Sat, Sun, or Sat & Sun) can
trigger
the 45 day flag as you set up?

Thanks so much!

Dan

"Steve Dunn" wrote:

you could use:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll on
Time",
IF(AND(K4="",TODAY()WORKDAY(H4,IF(N4="Hudson Sat. & Sun.",45,10),
AA4:AA12)), "Return Referral Form to Referent",""))))

but this (imho) is a little tidier:

=IF((H4="")+(K4""),"",IF(S4="Sent","Client Failed to Enroll on
Time",
IF((K4="")*(TODAY()WORKDAY(H4,10+35*(N4="Hudson Sat. & Sun."),
AA4:AA12)), "Return Referral Form to Referent","")))




"Danny Boy" wrote in message
...
The formula I have below in Column R works just fine. It creates
a
flag
for
me (in Column R) that lets me know one of two things: “Client
Failed
to
Enroll on Time”, and “Return Referral Form to Referent”. Here is
that
formula:

=IF(H4="","",IF(K4"","",IF(S4="Sent","Client Failed to Enroll
on
Time",IF(AND(K4="",TODAY()WORKDAY(H4,10,AA4:AA12) ),"Return
Referral
Form
to
Referent",""))))

The one change (addition) that 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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.