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

[Guest]

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!

 

[ScottO]

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

| 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!

 

[Guest]

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

 

[ScottO]

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

| 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
| >
message
| > | > | 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!
| >
| >
| >

 

[ScottO]

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

message | 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
|
| | | 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
| | >
| message
| | > | | > | 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!
| | >
| | >
| | >
|
|

 

[Roger Govier]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Myrna Larson]

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.