# Months or Years remaining, formatting

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

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"

Bruce

3. ## Re: Months or Years remaining, formatting

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

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

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk

"James C" <James.C.1vm5qa_1127145906.3348@excelforum-nospam.com> wrote in
message news:James.C.1vm5qa_1127145906.3348@excelforum-nospam.com...
>
> 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
>

4. ## Thanks Much

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1