Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Months or Years remaining, formatting
Hello all. =(A1-(NOW()))/365 The above formula gives me years remaining as "X.XX YEARS" (currently I have the cell formatted as 0.0" years"; ) This is a two-part question: (a) FORMULA CHANGE: I want to change the formula so that the result is the same, UNLESS [LESS THAN 1 YEAR], THEN "X.XX MONTHS" (b) FORMATTING METHOD: should i set up a conditional formatting solution, or can I just do this all in one in-cell formula? Any help would be much apprecieted. - Jim -- James C ------------------------------------------------------------------------ James C's Profile: http://www.excelforum.com/member.php...o&userid=12114 View this thread: http://www.excelforum.com/showthread...hreadid=468843 |
#2
|
|||
|
|||
Hi James. First, Cond. Formatting will not allow you to change the Number format. Only things like font, border and fill patterns can be set via CF. Try this: (format your output cell as General) =IF((A1-NOW())/365<1,ROUND(((A1-NOW())/365)*12,2)&" Months",ROUND(((A1-NOW())/365),2)&" Years") A1=01 July 2006 Now()=19 Sept 2005 result= "9.35 Months" Does this meet your needs? Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=468843 |
#4
|
|||
|
|||
Dear Swat and Sandy, Thanx much to you both. Swat's worked perfectly. Sandy yours is a nice idea but I have not implemented it as my problem was already solved. Made a note of it for future reference. Best Regards, - James -- James C ------------------------------------------------------------------------ James C's Profile: http://www.excelforum.com/member.php...o&userid=12114 View this thread: http://www.excelforum.com/showthread...hreadid=468843 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula that will represent years & months worked | Excel Discussion (Misc queries) | |||
calculate date differences in years and months | Excel Worksheet Functions | |||
Converting months to years | Excel Worksheet Functions | |||
Calculate months and years | Excel Discussion (Misc queries) | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |