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?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Years[/TD]
[TD]Months[/TD]
[TD]Days[/TD]
[TD]Desired format[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]6[/TD]
[TD]29[/TD]
[TD]32.57221081 yrs[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5[/TD]
[TD]13[/TD]
[TD]28.45311431 yrs[/TD]
[/TR]
</tbody>[/TABLE]


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.