Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF F'n Date logic (Win2000&up; MacOSX)

    I have a medical cache data set that includes expiration dates for some items, other items have "none". I used a nested IFstatement to check that a non-blank entry existed (so there is no ambiguity of whether it is missing or not) and then used a named variable LeadTime (integer in days) to calculate an expiration flag for ordering replacements in a timely manner. I now would like to suppress the #Value error that occurs when the value is none so I can just extend the equation throughout the column for the flag. I currently only use the equation on rows that have vaiid dates because of this error message. The equation I am using is:
    =IF(E7,(IF(E7<TODAY()+LeadTime,"T","")),"") which works perfectly as long as the referenced cell has a valid date. I will need to pass this workbook on to a less experienced user for maintenance, so I was trying to keep everything as simple as possible.
    Thanks for any help,
    Gloria

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF F'n Date logic (Win2000&up; MacOSX)

    A date is stored in Excel as a number, so you could test if the value in E7 is numeric:

    =IF(ISNUMBER(E7),(IF(E7<TODAY()+LeadTime,"T","")), "")

    If E7 contains a text, ISNUMBER returns FALSE, so the formula will result in the empty string "".

    Note: this formula does not test whether E7 contains a valid date, only whether E7 contains a numeric value.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF F'n Date logic (Win2000&up; MacOSX)

    You should only get a #VALUE error if E7 contains text, including a space. The following checks for blanks and/or text and returns a blank if either is found.

    <big>=IF(OR(ISBLANK(E7),ISTEXT(E7)),"",IF(E7<TODAY ()+LeadTime,"T",""))</big>

    or the following might also work

    <big>=IF(OR(ISBLANK(E7),ISTEXT(E7),E7>=TODAY()+Lea dTime),"","T")</big>

    Andrew C

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF F'n Date logic (Win2000&up; MacOSX)

    Thanks Hans. I should have thought of that, but it just never got through my blind spot. You guys always come through!
    [img]/forums/images/smilies/smile.gif[/img]
    Gloria

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF F'n Date logic (Win2000&up; MacOSX)

    Thank you, Andrew. For some reason I've never used the OR function and this reminds me to get out my rut and be more creative. This is a slick and easy to understand way to solve my problem. This forum is the best!
    [img]/forums/images/smilies/smile.gif[/img]
    Gloria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •