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

1. ## 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. ## 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 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. ## 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.

or the following might also work

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

Andrew C

4. ## 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. ## 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
•