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

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

Try:

=DATEDIF(A1,TODAY(),"y") & " Years " & DATEDIF(A1,TODAY(),"ym") & " Months"

DATEDIF is only documented in XL2000 Help but Chip Pearson has a page on his
website about it:

http://www.cpearson.com/excel/datedif.htm

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

"James C" wrote in
message ...

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

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