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 |

5/19/2010 3:45:01 PM

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

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 |

5/28/2010 7:16:26 AM