Date Difference

[Jaz]

How would I create a formula that takes 2 different dates and determines the
number of years between them to the nearst tenth of a year.

For example, the difference between 1/1/03 and 7/1/05 is 2.6 years.

If I use the DATEDIF function, I only get a rounded number.

Any ideas?

Thanks,
Jasper

 

[Stephen]

How would I create a formula that takes 2 different dates and determines
the
number of years between them to the nearst tenth of a year.

For example, the difference between 1/1/03 and 7/1/05 is 2.6 years.

If I use the DATEDIF function, I only get a rounded number.

Any ideas?

Thanks,
Jasper

Dates are stored as numbers where 1 represents 1 day. So, subtracting one
date from the other will give the number of days difference. Dividing by
365.25 will then give the number of years, which you can round to one
decimal place:
=ROUND((A2-A1)/365.25,1)
You will need to format the result cell as general or number.

BTW, the difference between your two dates is 2.5 years, not 2.6.

 

[Roger Govier]

Hi Jaz

In addition to Stephen's method, Datedif will give you more than just
round years if required
With 1/1/03 in A1, and 1/7/05 in B1
Try
=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months
"&DATEDIF(A1,B1,"md")&" days"
which will return
2 years 6 months 0 days
or in decimal format
=(DATEDIF(A21,B21,"y")+DATEDIF(A21,B21,"ym")/12)
which will return 2.5