Number of years/months/days between 2 dates

[Guest]

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

 

[Bondi]

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

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

 

[Guest]

Thank you very much Bondi

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

 

[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

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

 

[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

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