Number of years/months/days between 2 dates

D

Deborah

I am looking for a formula that gives me the number of years, months and days
between two dates.

e.g. 03/02/1998 - 20/07/2005 (dd/mm/yyyy)
Result: 7 years, 5 months, 17 days

e.g. 01/01/2005 - 01/03/2006
Result: 1 year, 2 months, 0 days

Thank you for your time.

Deborah
 
B

Bondi

Deborah said:
I am looking for a formula that gives me the number of years, months and days
between two dates.

e.g. 03/02/1998 - 20/07/2005 (dd/mm/yyyy)
Result: 7 years, 5 months, 17 days

e.g. 01/01/2005 - 01/03/2006
Result: 1 year, 2 months, 0 days

Thank you for your time.

Deborah
Click to expand...

Hi Deborah,

Maybe you can use something like this:

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, "
&DATEDIF(A1,A2,"md") & " days"

With the two dates in A1 and A2

Regards,
Bondi
 
D

Deborah

Thank you very much Bondi

Bondi said:
Hi Deborah,

Maybe you can use something like this:

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, "
&DATEDIF(A1,A2,"md") & " days"

With the two dates in A1 and A2

Regards,
Bondi
Click to expand...
 
R

Ron Rosenfeld

Hi Deborah,

Maybe you can use something like this:

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, "
&DATEDIF(A1,A2,"md") & " days"

With the two dates in A1 and A2

Regards,
Bondi
Click to expand...

Because of the way DATEDIF handles months with different days, there are some
potential pitfalls.

For example:

31-Jan-2005 1-Mar-2006 1 years, 1 months, -2 days

--ron
 
R

Ron Rosenfeld

I am looking for a formula that gives me the number of years, months and days
between two dates.

e.g. 03/02/1998 - 20/07/2005 (dd/mm/yyyy)
Result: 7 years, 5 months, 17 days

e.g. 01/01/2005 - 01/03/2006
Result: 1 year, 2 months, 0 days

Thank you for your time.

Deborah
Click to expand...

Because of the fact that neither years, nor months, always have the same number
of days, you can get unexpected results unless you define very clearly what you
want.

For example, Bondi's formula will work usually. But:

31-Jan-2005 1-Mar-2006 1 years, 1 months, -2 days


--ron
 
Top