Date Difference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jaz - 03 Jan 2006 16:40 GMT

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

Reply to this Message

Stephen - 03 Jan 2006 16:50 GMT

> How would I create a formula that takes 2 different dates and determines
> the
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

Roger Govier - 03 Jan 2006 18:12 GMT

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

Signature

Regards

Roger Govier

> 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
[quoted text clipped - 3 lines]
> Thanks,
> Jasper

Reply to this Message