some of the expiry dates are entered only as years e.g. 2013, 2025, and so
forth.

what can i include the formula you provided for excel to recognise these
entries as years and not date values?

thanks for your help already, it made the world of difference.

regards
--
Andre van Wyk


"Bernard Liengme" wrote:

=IF(ISBLANK(I2),"No
data",IF(ISTEXT(I2),I2,IF(I2<=TODAY(),"Expired",DA TEDIF(TODAY(),I2,"y")&"
years, "&DATEDIF(TODAY(),I2,"ym")&" months, "&DATEDIF(TODAY(),I2,"md")&"
days")))

For more on DATEDIFF, see http://www.cpearson.com/excel/datedif.htm
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andre van Wyk" wrote in message
...
I have a column of contract expiry dates, but not all cell values are
dates:
Some are empty, some contain "month to month", others "6 months", and so
forth.

I need a formula that will-
(1) return the years/month/days remaining on the contract (i.e. Today() -
the date in cell to the left=years/month/days; but
(2) if the result is a fraction or a negative (i.e. the date in the column
to the left is earlier than TODAY() it must return "EXPIRED"; and
(3) if the cell on the left is blank, return "NO DATA"; and
(4) if the cell to the left contains text (e.g. "month to month" or "6
months") it must return that text.

I can combine three of the above with the an IF function in an IF
function,
but Excel seems does not seem to have a formula that can contain more
conditions.

My formula is: =IF(ISBLANK(I2),"NO
DATA",IF(I2-TODAY()1,I2-TODAY(),"EXPIRED"))

Please, can someone help?
--
Andre van Wyk