Convert Decimal Ages to Days Months and Years

[Stephen_IV]

I have a list of decimal birthdates that I would like to convert to Days, months, and years.
Any help would be appreciated. Thanks in advance!

17.07123288 16.93150685 16.90136986 16.36712329 16.36712329 16.77808219 16.72054795 16.31780822 15.6109589 11.77260274 12.10410959 16.69315068 5.843835616 5.843835616 16.49589041 14.79726027 15.33150685 15.03561644

 

[awoohaw]

can you give us an example of what the values in the column mean: 17.07123288, excel will interpret that as 17 days 1 hr 42 minutes and some seconds old.

 

[Stephen_IV]

Sorry I was not clear, this is supposed to say 17 years old x, months and x, days. For example what is .07123288 equate to?

17 years 11 months and x days

13.2000000000000 13 years 2 months 14.3863013698630 14 years 4 months 11.8958904109589 11 years 10 months 9.5041095890411 9 years 5 months 15.8082191780822 15 years 9 months 13.2082191780822 13 years 2 months

 

[Skybluekid]

Try

Book1.xlsx
	D	E
3	17.07123	17 years 11 months
4	16.93151	16 years 12 months
5	16.90137	16 years 13 months
6	16.36712	16 years 32 months
7	16.36712	16 years 32 months
8	16.77808	16 years 15 months
9	16.72055	16 years 16 months
10	16.31781	16 years 37 months
11	15.61096	15 years 7 months
12	11.7726	11 years 6 months
13	12.10411	12 years 115 months
14	16.69315	16 years 17 months
15	5.843836	5 years 6 months
16	5.843836	5 years 6 months
17	16.49589	16 years 24 months
18	14.79726	14 years 15 months
19	15.33151	15 years 9 months
20	15.03562	15 years 11 months
Sheet2

Cell Formulas
Range	Formula
E3:E20	E3	=INT(D3)&" years "&INT(12/MOD(D3,2))&" months"

Not too sure about the days bit

 

[awoohaw]

Sorry I was not clear, this is supposed to say 17 years old x, months and x, days. For example what is .07123288 equate to?

17 years 11 months and x days

13.2000000000000 13 years 2 months 14.3863013698630 14 years 4 months 11.8958904109589 11 years 10 months 9.5041095890411 9 years 5 months 15.8082191780822 15 years 9 months 13.2082191780822 13 years 2 months

Sorry I was not clear, this is supposed to say 17 years old x, months and x, days. For example what is .07123288 equate to?

17 years 11 months and x days

13.2000000000000 13 years 2 months 14.3863013698630 14 years 4 months 11.8958904109589 11 years 10 months 9.5041095890411 9 years 5 months 15.8082191780822 15 years 9 months 13.2082191780822 13 years 2 months

how do you distinguish between 1 and 11 months, and 2 and 12 months? What are your rounding rules?

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	17.0712329	17 years 0 months 311 Days
3	16.9315068	16 years 11 months 65 Days
4	16.9013699	16 years 10 months 298 Days
5	16.3671233	16 years 4 months 147 Days
6	16.3671233	16 years 4 months 147 Days
7	16.7780822	16 years 9 months 122 Days
8	16.7205479	16 years 8 months 236 Days
9	16.3178082	16 years 3 months 297 Days
10	15.6109589	15 years 7 months 121 Days
11	11.7726027	11 years 9 months 98 Days
12	12.1041096	12 years 1 months 91 Days
13	16.6931507	16 years 8 months 115 Days
14	5.84383562	5 years 10 months 46 Days
15	5.84383562	5 years 10 months 46 Days
16	16.4958904	16 years 5 months 346 Days
17	14.7972603	14 years 9 months 206 Days
18	15.3315068	15 years 3 months 357 Days
19	15.0356164	15 years 0 months 156 Days
20	13.2	13 years 2 months 145 Days
21	14.3863014	14 years 4 months 231 Days
22	11.8958904	11 years 10 months 273 Days
23	9.50410959	9 years 6 months 18 Days
24	15.8082192	15 years 9 months 255 Days
25	13.2082192	13 years 2 months 182 Days
Data

Cell Formulas
Range	Formula
B2:B25	B2	=LET(m,MOD(A2,1)*12,INT(A2)&" years "&INT(m)&" months "&INT(MOD(m,1)*365)&" Days")

 

[awoohaw]

inconsistency in how you figure out months. please give the details.

13.20000000​	13 years 2 months	.2 is months
14.38630137​	14 years 4 months	rounded .38 to 4
11.89589041​	11 years 10 months	how is .89 rounded to 10?
9.50410959​	9 years 5 months	round down .5 to 5
15.80821918​	15 years 9 months	rounded .808 up to 9?
13.20821918​	13 years 2 months	rounded .208 to 2

 

[awoohaw]

How about

Fluff.xlsm
	A	B
1
2	17.0712329	17 years 0 months 311 Days
3	16.9315068	16 years 11 months 65 Days
4	16.9013699	16 years 10 months 298 Days
5	16.3671233	16 years 4 months 147 Days
6	16.3671233	16 years 4 months 147 Days
7	16.7780822	16 years 9 months 122 Days
8	16.7205479	16 years 8 months 236 Days
9	16.3178082	16 years 3 months 297 Days
10	15.6109589	15 years 7 months 121 Days
11	11.7726027	11 years 9 months 98 Days
12	12.1041096	12 years 1 months 91 Days
13	16.6931507	16 years 8 months 115 Days
14	5.84383562	5 years 10 months 46 Days
15	5.84383562	5 years 10 months 46 Days
16	16.4958904	16 years 5 months 346 Days
17	14.7972603	14 years 9 months 206 Days
18	15.3315068	15 years 3 months 357 Days
19	15.0356164	15 years 0 months 156 Days
20	13.2	13 years 2 months 145 Days
21	14.3863014	14 years 4 months 231 Days
22	11.8958904	11 years 10 months 273 Days
23	9.50410959	9 years 6 months 18 Days
24	15.8082192	15 years 9 months 255 Days
25	13.2082192	13 years 2 months 182 Days
Data

Cell Formulas
Range	Formula
B2:B25	B2	=LET(m,MOD(A2,1)*12,INT(A2)&" years "&INT(m)&" months "&INT(MOD(m,1)*365)&" Days")

The decimal is the portion of 365?

 

[Stephen_IV]

Thanks everyone for the insight. I am horrible with dates and times. Fluff thanks again I think that is on target with the days.

 

[Fluff]

Glad we could help & thanks for the feedback.