Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Combining multiple formulas in one cell

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Combining multiple formulas in one cell

=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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Combining multiple formulas in one cell

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Combining multiple formulas in one cell

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






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Combining multiple formulas in one cell

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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default Combining multiple formulas in one cell

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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining data from multiple cells into one cell. Steelsilk Excel Worksheet Functions 4 May 17th 07 06:54 PM
Combining multiple cell information into one cell Pete Elbert Excel Discussion (Misc queries) 0 December 13th 06 12:36 PM
Combining formulas, "and" & "or" to verify content of multiple cel Shu of AZ Excel Discussion (Misc queries) 15 October 15th 06 11:22 PM
Most efficient formula/combining multiple data cell ranges/seperat Tiff New Users to Excel 1 August 2nd 06 04:56 PM
Combining data (numeric format) in multiple cells into one cell (t GNAC SID Excel Discussion (Misc queries) 2 February 7th 05 05:09 PM


All times are GMT +1. The time now is 11:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"