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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans. I should have thought of that, but it just never got through my blind spot. You guys always come through!

Gloria

Reply | Quote

Thanks Hans. I should have thought of that, but it just never got through my blind spot. You guys always come through!

Gloria

Reply | Quote

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.

Reply | Quote

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.

=IF(OR(ISBLANK(E7),ISTEXT(E7)),””,IF(E7<TODAY()+LeadTime,"T",""))

or the following might also work

=IF(OR(ISBLANK(E7),ISTEXT(E7),E7>=TODAY()+LeadTime),””,”T”)

Andrew C

Reply | Quote

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!

Gloria

Reply | Quote

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!

Gloria

Reply | Quote

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.

=IF(OR(ISBLANK(E7),ISTEXT(E7)),””,IF(E7<TODAY()+LeadTime,"T",""))

or the following might also work

=IF(OR(ISBLANK(E7),ISTEXT(E7),E7>=TODAY()+LeadTime),””,”T”)

Andrew C

Reply | Quote