Conversion of Years, Months and Days into a product of Years with decimals.

[Kevinlzh]

Hi,

I need assistance with the converting and combining number of years, months and days into a number with decimal places.

For example i have known a friend for 32yrs, 6mths and 29 days; how do i convert an combine the duration i have known him for into a number with decimal places?

Years	Months	Days	Desired format
32	6	29	32.57221081 yrs
26	5	13	28.45311431 yrs

<tbody>
</tbody>

Pls enlighten me.

 

[Col Delane]

Let's say the table in your post is in the range A1:D3
Now, we know that all years are made up of 12 months, and lets assume all years last 365 days (ignoring leap years as 1 day ain't gonna make diddly squat of difference in your decimal year!)

So, in D2 enter the following formula, then copy down column D:
= A2 + B2/12 + C2/365
Note however that this calc. produces a different result to what you show (but I don't know what math's you applied):
32.606849 vs 32.57221081 yrs
26.452283 vs 28.45311431 yrs

If you want to round the result to say two decimal places, use:
= Round( A2 + B2/12 + C2/365, 2)

 

[Joe4]

Assuming that your first row of data is in row 2 in columns A, B, and C, enter this formula in D2:

=A2+(B2/12)+(C2/365.25)

Note there might be a slight difference in the decimal portion, due to how you are handling days (how many days in a month? 30? 31?).
I went with the average of 365.25 days in a year (every 4 years there are 366).

 

[BobUmlas]

There's no exact answer, because a month could have 28, 29, 30, or 31 days, and you gave no starting date. Closest formula would be
=A2+B2/12+C2/365
filled down

 

[Joe4]

Closest formula would be
=A2+B2/12+C2/365

May be nitpicking, but since the examples are for large amounts of years (26, 32), I would say dividing by 365.25 would be the closest formula, as there would be at least 6 leap years in those two examples.