Add decimal years to a date

M

Michael Lock

How do I add decimal years to a date. I know that I have 9.79 years untill
full retirement. How do I add that to today's date to find out what my
retirement date is?
 
G

Guest

Hi

Try multiplying your 9.79 by 365 - as 1 in Excel is one day. You can then
add this to your date and format the result as a date.

Andy.
 
M

Michael Lock

Yea. I'd thought of that. The only drawback there is it doesn't deal with
leap years. I know that there should be two in the next 9.79 years (so I
could subtract 2 from the total number of days I calculate) but I was hoping
that there might be some method which automatically accounted for that.
 
R

Ron Rosenfeld

How do I add decimal years to a date. I know that I have 9.79 years untill
full retirement. How do I add that to today's date to find out what my
retirement date is?
Click to expand...

The problem with using decimal years is that both years and months have
different numbers of days. So it's going to depend on what sort of assumptions
you want to make.

You could assume, for example, that the average year has 365.25 days and use a
formula like:

=A1+365.25*B1

Where A1 is your date; and B1 is your decimal years.

You could assume the last month has 30 days and use the formula:

=DATE(YEAR(A1)+INT(B1),MONTH(A1)+MOD(
B1,1)*12,DAY(A1)+MOD(MOD(B1,1)*12,1)*30)

or you could assume an average month has 365/12 days and use:

=DATE(YEAR(A1)+INT(B1),MONTH(A1)+MOD(
B1,1)*12,DAY(A1)+MOD(MOD(B1,1)*12,1)*365/12)

There might be a one or two day difference between the two formulas.

Or you could find out how your company calculates it, and see if things are
close.


--ron
 
M

Michael Lock

Thanks Ron. I think this is as close as I'm going to get. I just thought it
should be easier than it appears to be. ;-)
 
P

Pete_UK

How do you know that it is 9.79 years to retirement? How did you (or
somebody else) arrive at this figure? Presumably if you knew how this
was calculated then you could apply the appropriate formula within
Excel.

Pete
 
Top