Combining multiple formulas in one cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andre van Wyk - 11 Jun 2007 19:31 GMT

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?

Signature

Andre van Wyk

Reply to this Message

Bernard Liengme - 11 Jun 2007 20:13 GMT

=IF(ISBLANK(I2),"No
data",IF(ISTEXT(I2),I2,IF(I2<=TODAY(),"Expired",DATEDIF(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

Signature

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

>I have a column of contract expiry dates, but not all cell values are
>dates:
[quoted text clipped - 19 lines]
>
> Please, can someone help?

Reply to this Message

Andre van Wyk - 13 Jun 2007 12:45 GMT

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

Signature

Andre van Wyk

> =IF(ISBLANK(I2),"No
> data",IF(ISTEXT(I2),I2,IF(I2<=TODAY(),"Expired",DATEDIF(TODAY(),I2,"y")&"
[quoted text clipped - 27 lines]
> >
> > Please, can someone help?

Reply to this Message

Bernard Liengme - 13 Jun 2007 21:00 GMT

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

Signature

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

> some of the expiry dates are entered only as years e.g. 2013, 2025, and so
> forth.
[quoted text clipped - 40 lines]
>> >
>> > Please, can someone help?

Reply to this Message

Andre van Wyk - 14 Jun 2007 07:06 GMT

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

Signature

Andre van Wyk

> Thanks for the feedback.
> Recall that Excel stores dates as numbers: a count of how many days since
[quoted text clipped - 50 lines]
> >> >
> >> > Please, can someone help?

Reply to this Message

Bernard Liengme - 14 Jun 2007 13:09 GMT

Thanks for feedback. Private emails are welcome - just remove TRUENORTH.

Signature

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

> thanks bernard, you're a star.
>
[quoted text clipped - 68 lines]
>> >> >
>> >> > Please, can someone help?

Reply to this Message