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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com