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?

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

"Khurum" wrote in message
...
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?

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


Replace @mailinator.com with @tiscali.co.uk


"Khurum" wrote in message
...
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?

=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)



"Khurum" wrote in message
...
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?

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


In article ,
Khurum wrote:

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?

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


In article ,
"Bob Phillips" wrote:

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

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

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

Thanks to everyone. Problem completely solved.

"Rick Rothstein (MVP - VB)" wrote:

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)" wrote in
message ...
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.




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.


--

Regards,

Peo Sjoblom

In article ,
"Peo Sjoblom" wrote:

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.

"JE McGimpsey" wrote in message
...
In article ,
"Peo Sjoblom" wrote:

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.


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



--

Regards,

Peo Sjoblom

"David Biddulph" wrote:

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

"Khurum" wrote in message
...
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?



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.