Display months to Year - Month

[Diego Cota]

Hello everybody,

I've been searching for a way to display a number, e.g. 18 to "1 - 6", meaning 1 year, 6 months.

Traditional formulas like =INT(cell/12)&" - "&mod(cell,12), fail because it works fine with the rules of natural numbers (as expected) but not in how we count months, the first month is 1.

Need a formula without a dubious IF

See a typical example

INT(CELL/12) MOD(CELL,12) DESIRED
1 0 1 1
2 0 2 2
3 0 3 3
4 0 4 4
5 0 5 5
6 0 6 6
7 0 7 7
8 0 8 8
9 0 9 9
10 0 10 10
11 0 11 11
12 0 0 12
13 1 1 1
14 1 2 2
15 1 3 3
16 1 4 4
17 1 5 5
18 1 6 6
19 1 7 7
20 1 8 8
21 1 9 9
22 1 10 10
23 1 11 11
24 1 0 12
25 2 1 1

Thanks for any suggestion.

Diego

 

[vletm]

if B-column has Your numbers then test this formula
=INT((B3-1)/12) & "-" & B3-INT((B3-1)/12)*12

 

[Diego Cota]

It worked. The solution is very elegant. Thank you!