Combining multiple formulas in one cell

A

Andre van Wyk

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?
 
A

Andre van Wyk

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
 
B

Bernard Liengme

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
 
A

Andre van Wyk

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search closest day in the past from today 2
Use TODAY() function as the criteria in a SUMIF formula 1
Show value from one cell in another cell if today falls between a date range 5
Conditional Formatting Conundrum 3
Problem with "IF" function 9
use month in cell date to sum all relevant cells 2
YTD SUMIF based on current date 3
Multiple If statements, Calculating quarterly % holding costs 0

Top