Way to format number as "X Years, Y Months"??

[MarcSebens]

Hello, first post in search of a solution, so please forgive any lapse of protocol. I did attempt to search for a solution before posting - not my first time on any forum at least!

I need the term of a lease in my spreadsheet to display as "X Years, Y Months" but would like to be able to use the cell as a reference for other cell formulas. I know I can create a custom number format for "5" to read "5 Years" (# "Years") but can't figure out a way to break it into "X Years, Y Months" without using 2 cells.

For example - if the value in a cell is 66 (months), how can I write a custom number format to make it read "5 Years, 6 Months" while being able to link formulas in dependent cells back to the "66" cell value?

Many thanks for anybody that can help.

 

[tweedle]

Hi Marc,

Welcome to the forum.

I'm not sure you're going to get a format to perform the math calculation for both sides of your statement (years & months)
Your example with the 5 is fine, because no math operations are needed to represent the outcome.
However the 66 requires math operations to derive both values.

You may be forced to use an additional formula.

	C	D
4 Years 10 Months
4 Years 11 Months
5 Years 0 Months
5 Years 1 Months
5 Years 2 Months
5 Years 3 Months
5 Years 4 Months
5 Years 5 Months
5 Years 6 Months

<colgroup><col style="width: 30px;"><col style="width: 64px;"><col style="width: 118px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"]58[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"]59[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"]60[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: right"]61[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"]62[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"]63[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"]64[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: right"]65[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: right"]66[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D11 =ROUNDDOWN(C11/12,0)&" Years "&MOD(C11,12)&" Months" D12 =ROUNDDOWN(C12/12,0)&" Years "&MOD(C12,12)&" Months" D13 =ROUNDDOWN(C13/12,0)&" Years "&MOD(C13,12)&" Months" D14 =ROUNDDOWN(C14/12,0)&" Years "&MOD(C14,12)&" Months" D15 =ROUNDDOWN(C15/12,0)&" Years "&MOD(C15,12)&" Months" D16 =ROUNDDOWN(C16/12,0)&" Years "&MOD(C16,12)&" Months" D17 =ROUNDDOWN(C17/12,0)&" Years "&MOD(C17,12)&" Months" D18 =ROUNDDOWN(C18/12,0)&" Years "&MOD(C18,12)&" Months" D19 =ROUNDDOWN(C19/12,0)&" Years "&MOD(C19,12)&" Months" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jon Peltier]

Assuming you have two dates and are formatting the result of a formula in a third cell which subtracts the earlier from the later.

For example, if I put 1/15/2014 into A1 and 5/1/2010 into A2, then put =A1-A2 into A3, the result is 1349 which is the difference between the dates in days.

If I format this as a date, I get 9/10/1903.

If I format using the custom format Y" years "M" months" I get 03 years 9 months.

 

[MarcSebens]

These are great. Thank you both for the help.