Thread: Number of years/months/days between 2 dates

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

Deborah wrote:
> 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

Thank you very much Bondi

"Bondi" wrote:

>
> Deborah wrote:
> > 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
>
>

On 7 Jul 2006 01:32:46 -0700, "Bondi" <mbondorff@hotmail.com> wrote:

>
>Deborah wrote:
>> 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

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

On Fri, 7 Jul 2006 01:06:01 -0700, Deborah <Deborah@discussions.microsoft.com>
wrote:

>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