Prev: need excel worksheet help, running total based on date
Next: Formula to estimate value
From: Warren Jo on 28 Feb 2010 18:14
I'm using this formula to return a 'current' or 'expired' value depending on
the date in column B (expiry after one year). Because I want to autofill
rows that don't yet have data, I require some extra criteria so that no value
is returned if B is still blank. Comprehend??

=IF((TODAY()-B4>365), "EXPIRED", "CURRENT")

It may be a bit clumsy, so comments on this base formula would also be
appreciated.
From: zvkmpw on 28 Feb 2010 19:57
> I'm using this formula to return a 'current' or 'expired' value depending on
> the date in column B (expiry after one year).  Because I want to autofill
> rows that don't yet have data, I require some extra criteria so that no value
> is returned if B is still blank.  Comprehend??
>
> =IF((TODAY()-B4>365), "EXPIRED", "CURRENT")

Maybe something like this would help:
=IF(B4="","",IF((TODAY()-B4>365), "EXPIRED", "CURRENT"))



From: OssieMac on 28 Feb 2010 20:19
Hi Warren,

Try the following.

=IF(B4="","",IF((TODAY()-B4>365), "EXPIRED", "CURRENT"))

--
Regards,

OssieMac


"Warren Jo" wrote:

> I'm using this formula to return a 'current' or 'expired' value depending on
> the date in column B (expiry after one year). Because I want to autofill
> rows that don't yet have data, I require some extra criteria so that no value
> is returned if B is still blank. Comprehend??
>
> =IF((TODAY()-B4>365), "EXPIRED", "CURRENT")
>
> It may be a bit clumsy, so comments on this base formula would also be
> appreciated.
From: Dana DeLouis on 1 Mar 2010 07:52
On 2/28/2010 6:14 PM, Warren Jo wrote:
> I'm using this formula to return a 'current' or 'expired' value depending on
> the date in column B (expiry after one year). Because I want to autofill
> rows that don't yet have data, I require some extra criteria so that no value
> is returned if B is still blank. Comprehend??
>
> =IF((TODAY()-B4>365), "EXPIRED", "CURRENT")
>
> It may be a bit clumsy, so comments on this base formula would also be
> appreciated.

Hi. Another option:

=IF(ISBLANK(B4),"",IF(EDATE(B4,12) > TODAY(),"Expired","Current"))


= = = = = = =
HTH :>)
Dana DeLouis
 | 
Pages: 1
Prev: need excel worksheet help, running total based on date
Next: Formula to estimate value