PDA

View Full Version : Format an decimal as month


nikeshtnt
Aug 3, 2009, 09:50 PM
How can 6.08 can be shown as 6 Yrs 1 Month and so on number of month would change like for 0.17 as 2, 0.25 as 3, 0.33 as 4...

If condition is not working with Int() for more than 6 condition.

Please help.

ROLCAM
Aug 3, 2009, 10:28 PM
These are the twelve decimals.

1 0.083333
2 0.166667
3 0.25
4 0.333333
5 0.416667
6 0.5
7 0.583333
8 0.666667
9 0.75
10 0.833333
11 0.916667
12 1.0

JBeaucaire
Aug 3, 2009, 11:35 PM
Play around with this:

=INT(A1) & " years and " & LOOKUP(MOD(A1, 1), {0,0.833,0.167,0.25,0.333,0.417,0.5,0.583,0.667,0. 75,0.833,0.917}, {0,1,2,3,4,5,6,7,8,9,10,11}) & " months"