Date Difference

  • Thread starter Thread starter Jaz
  • Start date Start date
J

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
 
Jaz said:
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
Click to expand...

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.
 
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
 
Back
Top