Combining multiple formulas in one cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Thanks for the feedback.
Recall that Excel stores dates as numbers: a count of how many days since
1/1/1900
So I changed the formula to
=IF(ISBLANK(I2),"Nodata",IF(ISTEXT(I2),I2,IF(I2<36000,I2-YEAR(TODAY())&"
years",IF(I2<=TODAY(),"Expired",DATEDIF(TODAY(),I2,"y")&" years,
"&DATEDIF(TODAY(),I2,"ym")&" months, "&DATEDIF(TODAY(),I2,"md")&"days"))))
The vale 3600o was picked somewhat arbitrarily; it corresponds to
27/July/1998 (You could make it 10000 if you wished)
best wishes
 
thanks bernard, you're a star.

i fiddled a bit with the spaces and finally i have a working formula.

also, it would be great if if i could ask you directly for help in future.

the ms excel help files realy lack in effectively communicating how
functions work to build effective formulas.

regards
 
Back
Top