How do I calculate the difference between 2 dates (m,d,y) ?

How do I calculate the difference between 2 dates (m,d,y) ?
For example :
date1 = 19 feb 1999
date2 = 07 ian 2005
difference = **days, **months, **years.
thank you!

This may not be the *best* way, but it looks like it works ...

In A1 put Date1 (eg 19 Feb 1999)
In A2 put Date2 (eg 07 Jan 2005)
In B1 put the formula =INT(YEARFRAC(A1,A2,1))
In B2 put the formula =INT((YEARFRAC(A1,A2,1)-B1)*12)
In B3 put the formula =A2-DATE(YEAR(A1)+B1,MONTH(A1)+B2,DAY(A1))
In C1 put the text "Years"
In C2 put the text "Months"
In C3 put the text "Days"

If you need to get the result all into one cell, you can make a
compound formula, or concatenate the results as you wish.

HTH
ScottO

"ady_sandu" <[email protected]> wrote in message
news:[email protected]...
| How do I calculate the difference between 2 dates (m,d,y) ?
| For example :
| date1 = 19 feb 1999
| date2 = 07 ian 2005
| difference = **days, **months, **years.
| thank you!

the function *INT(YEARFRAC(A1,A2,1))* it is not recognized by excel...

"ScottO" wrote:

> This may not be the *best* way, but it looks like it works ...
>
> In A1 put Date1 (eg 19 Feb 1999)
> In A2 put Date2 (eg 07 Jan 2005)
> In B1 put the formula =INT(YEARFRAC(A1,A2,1))
> In B2 put the formula =INT((YEARFRAC(A1,A2,1)-B1)*12)
> In B3 put the formula =A2-DATE(YEAR(A1)+B1,MONTH(A1)+B2,DAY(A1))
> In C1 put the text "Years"
> In C2 put the text "Months"
> In C3 put the text "Days"
>
> If you need to get the result all into one cell, you can make a
> compound formula, or concatenate the results as you wish.
>
> HTH
> ScottO
>
> "ady_sandu" <[email protected]> wrote in message
> news:[email protected]...
> | How do I calculate the difference between 2 dates (m,d,y) ?
> | For example :
> | date1 = 19 feb 1999
> | date2 = 07 ian 2005
> | difference = **days, **months, **years.
> | thank you!
>
>
>

Sorry ady_, I should have mentioned that the "YearFrac" function is
an add-in.
All you need to do is load the Analysis ToolPak add-in and it'll work
fine.
Go Tools\Add-ins and check the box next to Analysis ToolPak, then
click OK.
Rgds,
ScottO

"ady_sandu" <[email protected]> wrote in message
news:[email protected]...
| the function *INT(YEARFRAC(A1,A2,1))* it is not recognized by
excel...
|
| "ScottO" wrote:
|
| > This may not be the *best* way, but it looks like it works ...
| >
| > In A1 put Date1 (eg 19 Feb 1999)
| > In A2 put Date2 (eg 07 Jan 2005)
| > In B1 put the formula =INT(YEARFRAC(A1,A2,1))
| > In B2 put the formula =INT((YEARFRAC(A1,A2,1)-B1)*12)
| > In B3 put the formula =A2-DATE(YEAR(A1)+B1,MONTH(A1)+B2,DAY(A1))
| > In C1 put the text "Years"
| > In C2 put the text "Months"
| > In C3 put the text "Days"
| >
| > If you need to get the result all into one cell, you can make a
| > compound formula, or concatenate the results as you wish.
| >
| > HTH
| > ScottO
| >
| > "ady_sandu" <[email protected]> wrote in
message
| > news:[email protected]...
| > | How do I calculate the difference between 2 dates (m,d,y) ?
| > | For example :
| > | date1 = 19 feb 1999
| > | date2 = 07 ian 2005
| > | difference = **days, **months, **years.
| > | thank you!
| >
| >
| >

If you don't like my answer, and I wouldn't blame you ...
Take a look at this link http://www.meadinkent.co.uk/xl_birthday.htm
Rgds,
ScottO

"ScottO" <[email protected]> wrote in
message news:[email protected]...
| Sorry ady_, I should have mentioned that the "YearFrac" function is
| an add-in.
| All you need to do is load the Analysis ToolPak add-in and it'll
work
| fine.
| Go Tools\Add-ins and check the box next to Analysis ToolPak, then
| click OK.
| Rgds,
| ScottO
|
| "ady_sandu" <[email protected]> wrote in message
| news:[email protected]...
| | the function *INT(YEARFRAC(A1,A2,1))* it is not recognized by
| excel...
| |
| | "ScottO" wrote:
| |
| | > This may not be the *best* way, but it looks like it works ...
| | >
| | > In A1 put Date1 (eg 19 Feb 1999)
| | > In A2 put Date2 (eg 07 Jan 2005)
| | > In B1 put the formula =INT(YEARFRAC(A1,A2,1))
| | > In B2 put the formula =INT((YEARFRAC(A1,A2,1)-B1)*12)
| | > In B3 put the formula
=A2-DATE(YEAR(A1)+B1,MONTH(A1)+B2,DAY(A1))
| | > In C1 put the text "Years"
| | > In C2 put the text "Months"
| | > In C3 put the text "Days"
| | >
| | > If you need to get the result all into one cell, you can make a
| | > compound formula, or concatenate the results as you wish.
| | >
| | > HTH
| | > ScottO
| | >
| | > "ady_sandu" <[email protected]> wrote in
| message
| | > news:[email protected]...
| | > | How do I calculate the difference between 2 dates (m,d,y) ?
| | > | For example :
| | > | date1 = 19 feb 1999
| | > | date2 = 07 ian 2005
| | > | difference = **days, **months, **years.
| | > | thank you!
| | >
| | >
| | >
|
|

Hi Ady

One way would be to use the little documented DATEDIF function

With your first date in A1 and your second date in A2
=DATEDIF(A1,A2,"md")&" days, "&DATEDFIF(A1,A2,"ym")&" months,
"&DATEDIF(A1,A2,"y")&" years"

Regards

Roger Govier


ady_sandu wrote:
> How do I calculate the difference between 2 dates (m,d,y) ?
> For example :
> date1 = 19 feb 1999
> date2 = 07 ian 2005
> difference = **days, **months, **years.
> thank you!

On Thu, 29 Sep 2005 09:15:42 +0100, Roger Govier
<[email protected]> wrote:

>Hi Ady
>
>One way would be to use the little documented DATEDIF function
>
>With your first date in A1 and your second date in A2
>=DATEDIF(A1,A2,"md")&" days, "&DATEDFIF(A1,A2,"ym")&" months,
>"&DATEDIF(A1,A2,"y")&" years"
>
>Regards
>
>Roger Govier
>
>

One of my peeves with the DATEDIF function is that it can give strange results
under certain circumstances. For example:

A1: 31 Jan 2005
A2 1 Mar 2007

-2 days, 1 months, 2 years


--ron

On Wed, 28 Sep 2005 22:04:02 -0700, ady_sandu
<[email protected]> wrote:

>How do I calculate the difference between 2 dates (m,d,y) ?
>For example :
>date1 = 19 feb 1999
>date2 = 07 ian 2005
>difference = **days, **months, **years.
>thank you!

Since both years and months have varying numbers of days, how you calculate the
differences depends on how you define these terms and how you want to calculate
the differences.

For example:

What result do you want for the following pairs of dates?

Start End
1/31/2005 3/1/2005

2/1/2005 3/1/2005

1/31/2005 5/1/2005

1/15/2005 3/15/2005


--ron

This will NOT always work correctly.

Errors are most likely when the 2nd date is very close to the anniversary
date. For example, with the dates Sep 29, 2000 and Sep 29, 2005. The result is
4 years, 11 months, and 31 days, instead of 5 years, 0 months, and 0 days.

(OTOH, with the first date Sep 29, 2001, you do get 4 years exactly.)

Based on much experimentation that Norman Harker and I did some time ago, the
"problems" arise from the bizarre way in which this function calculates the
number of days in year (the average of the year lengths starting with the
calendar year in which the 1st date falls and ending with the calendar year in
which the last day falls). We concluded that YEARFRAC was intended for
calculation of time spans less of than a year, and even then you have problems
if the dates are in two different years and one of them is a leap year.


On Thu, 29 Sep 2005 17:34:06 +1000, "ScottO"
<[email protected]> wrote:

>This may not be the *best* way, but it looks like it works ...
>
>In A1 put Date1 (eg 19 Feb 1999)
>In A2 put Date2 (eg 07 Jan 2005)
>In B1 put the formula =INT(YEARFRAC(A1,A2,1))
>In B2 put the formula =INT((YEARFRAC(A1,A2,1)-B1)*12)
>In B3 put the formula =A2-DATE(YEAR(A1)+B1,MONTH(A1)+B2,DAY(A1))
>In C1 put the text "Years"
>In C2 put the text "Months"
>In C3 put the text "Days"
>
>If you need to get the result all into one cell, you can make a
>compound formula, or concatenate the results as you wish.
>
>HTH
>ScottO
>
>"ady_sandu" <[email protected]> wrote in message
>news:[email protected]...
>| How do I calculate the difference between 2 dates (m,d,y) ?
>| For example :
>| date1 = 19 feb 1999
>| date2 = 07 ian 2005
>| difference = **days, **months, **years.
>| thank you!
>