Difference between dates

[Guest]

Hi

I need to work out the difference between todays date and a previous date,
but I want the answer in this format:

Years & Months & Days

Is this possible? I can use today date minus the date and change the format
to show the difference in days. Any way to then change this to the needed
format?

 

[David Biddulph]

You need the DATEDIF function:
http://www.cpearson.com/excel/datedif.htm

 

[Sandy Mann]

Earlier date in A1, later date in A2:

=DATEDIF(A1,A2,"y")&" Years "&DATEDIF(A1,A2,"ym")&" Months
"&DATEDIF(A1,A2,"md")&" Days"

If you want the format to show the ampersands as well then include extra &'s
in the quoted text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bob Phillips]

=DATEDIF(A1,A2,"Y")&" years "&DATEDIF(A1,A2,"YM")&" months
"&DATEDIF(A1,A2,"MD")&" days"

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

No way to do it with format.

You can use DateDif() (e.g.:

A3: =DATEDIF(A1, A2, "y") & " & " & DATEDIF(A1, A2, "ym") & " & " &
DATEDIF(A1, A2, "md")

The problem is that "months" is a squirrelly concept. For instance, is
28 February 2007 one month and zero days after 31 January 2007? If so,
then is it *also* one month and zero days after 29 January?

DATEDIF bases month length on the length of the month in the first
argument. So the above formula returns

A1: 31 January 2007
A2: 28 February 2007
A3: 0 & 0 & 28

but then:

A1: 31 January 2007
A2: 1 March 2007
A3: 0 & 0 & -2

 

[JE McGimpsey]

See my other post to see why this may not return the values you expect...

 

[Rick Rothstein \(MVP - VB\)]

I need to work out the difference between todays date and a previous date,

but I want the answer in this format:

Years & Months & Days

Is this possible? I can use today date minus the date and change the
format
to show the difference in days. Any way to then change this to the needed
format?

Your "format" is not exactly precise. Do you want your answer to look like
this...

5 & 3 & 21

or like this...

5 years & 3 months & 21 days

or did you want each date part in separate cells? Assuming you wanted the
second format I listed, try this formula...

=DATEDIF(A1,A2,"Y")&" years & "&DATEDIF(A1,A2,"YM")&" months &
"&DATEDIF(A1,A2,"MD")&" days"

Rick

 

[Rick Rothstein \(MVP - VB\)]

The problem is that "months" is a squirrelly concept. For instance, is

28 February 2007 one month and zero days after 31 January 2007? If so,
then is it *also* one month and zero days after 29 January?

No, 28-February-2007 is 28 days after 31-January-2007 and it is 30 days
after 29-January-2007 in the same way 30-April-2007 is 30 days after
31-March-2007. And, just like 1-May-2007 is 1 month and 0 days after
31-March-2007, 1-March-2007 should be 1 month and 0 days after
31-January-2007.

DATEDIF bases month length on the length of the month in the first
argument. So

A1: 31 January 2007
A2: 1 March 2007
A3: 0 & 0 & -2

Minus 2 days? That is just a ridiculous way to handle it. If you are
counting with months, then whenever you skip over a month, that is 1 month,
no matter how many days it has in it. DATEDIF has no trouble starting on the
last day of a 31-day month and skipping over a 30-day month to get to the
first of the following month, so why should the shorter month of February be
any different.

Rick

 

[Guest]

Thanks to everyone. Problem completely solved.

Your "format" is not exactly precise. Do you want your answer to look like
this...

5 & 3 & 21

or like this...

5 years & 3 months & 21 days

or did you want each date part in separate cells? Assuming you wanted the
second format I listed, try this formula...

=DATEDIF(A1,A2,"Y")&" years & "&DATEDIF(A1,A2,"YM")&" months &
"&DATEDIF(A1,A2,"MD")&" days"

Rick

 

[Peo Sjoblom]

No, 28-February-2007 is 28 days after 31-January-2007 and it is 30 days
after 29-January-2007 in the same way 30-April-2007 is 30 days after
31-March-2007. And, just like 1-May-2007 is 1 month and 0 days after
31-March-2007, 1-March-2007 should be 1 month and 0 days after
31-January-2007.


Minus 2 days? That is just a ridiculous way to handle it. If you are
counting with months, then whenever you skip over a month, that is 1
month, no matter how many days it has in it. DATEDIF has no trouble
starting on the last day of a 31-day month and skipping over a 30-day
month to get to the first of the following month, so why should the
shorter month of February be any different.

Did you try to your own formula with 01/31/07 in A1 and 03/01/07 in A2?
It returns

0 years & 1 months & -2 days

so are you saying that most people when asked how many months and days there
are between those dates would say 1 month and -2 days. Me thinks not and
that is the error in DATEDIF that John explained.

 

[JE McGimpsey]

and that is the error in DATEDIF

Rather than an error in DATEDIF, which works very consistently, if not
always the way we want it to), it's inherent in *any* deterministic
algorithm that I've ever seen used.

"Month", as a unit of time, is just too fluid a concept to be captured
algorithmically.

For instance, using Rick's definition, 1 March is 1 month and 0 days
after 31 January. One way of interpreting that is that 1 March is also 1
month and 0 days after 28 January, 29 January, and 30 January. That way
leads to madness when one wants a single result when trying to calculate
a date 1 month prior to 1 March.

That definition also causes strangeness when the first month is shorter
than the final one:

Start Date End Date Months/Days
28 Feb 28 Mar 0/28?
28 Feb 29 Mar 0/29? or 1/1?
28 Feb 30 Mar 0/30? or 1/2?
28 Feb 31 Mar 0/31? or 1/3?
28 Feb 1 Apr 1/0? or 1/4?
01 Mar 01 Apr 0/30? or 1/0?

in either case there's a discontinuity in the function...

Those of us who've been around here a while may remember herculean
efforts lead by Norman Harker to better DATEDIF with a self-consistent
algorithm, and the frustration of not being able to do so.

 

[Peo Sjoblom]

Rather than an error in DATEDIF, which works very consistently, if not
always the way we want it to), it's inherent in *any* deterministic
algorithm that I've ever seen used.

true, I should have expressed myself better.

"Month", as a unit of time, is just too fluid a concept to be captured
algorithmically.

For instance, using Rick's definition, 1 March is 1 month and 0 days
after 31 January. One way of interpreting that is that 1 March is also 1
month and 0 days after 28 January, 29 January, and 30 January. That way
leads to madness when one wants a single result when trying to calculate
a date 1 month prior to 1 March.

That definition also causes strangeness when the first month is shorter
than the final one:

Start Date End Date Months/Days
28 Feb 28 Mar 0/28?
28 Feb 29 Mar 0/29? or 1/1?
28 Feb 30 Mar 0/30? or 1/2?
28 Feb 31 Mar 0/31? or 1/3?
28 Feb 1 Apr 1/0? or 1/4?
01 Mar 01 Apr 0/30? or 1/0?

in either case there's a discontinuity in the function...

Those of us who've been around here a while may remember herculean
efforts lead by Norman Harker to better DATEDIF with a self-consistent
algorithm, and the frustration of not being able to do so.

Yes I remember that

 

[BazzaH]

You need the DATEDIF function:
http://www.cpearson.com/excel/datedif.htm
--
David Biddulph




Thanks to you all,

I have just moved from Lotus and didn't think my "Birthday Sheet" would
work, Years, Months & Days. But thanks to you I have found the hidden
"Datedif" again.