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

=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

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

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<36 000,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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andre van Wyk" wrote in message
...
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

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
--
Andre van Wyk


"Bernard Liengme" wrote:

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<36 000,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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andre van Wyk" wrote in message
...
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

Thanks for feedback. Private emails are welcome - just remove TRUENORTH.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andre van Wyk" wrote in message
...
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
--
Andre van Wyk


"Bernard Liengme" wrote:

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<36 000,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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Andre van Wyk" wrote in message
...
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