A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculate diiference between 2 dates



 
 
Thread Tools Display Modes
  #1  
Old March 4th, 2007, 10:56 AM posted to microsoft.public.excel.misc
Jester
external usenet poster
 
Posts: 11
Default Calculate diiference between 2 dates

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust


  #2  
Old March 4th, 2007, 11:17 AM posted to microsoft.public.excel.misc
mike
external usenet poster
 
Posts: 3,942
Default Calculate diiference between 2 dates

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

should do the trick with the dates in A1 & A2

Mike

"Jester" wrote:

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust



  #3  
Old March 4th, 2007, 11:20 AM posted to microsoft.public.excel.misc
Niek Otten
external usenet poster
 
Posts: 2,533
Default Calculate diiference between 2 dates

Hi John,

=DATEDIF(A1,A2,"y") & " Years " & DATEDIF(A1,A2,"ym") & " months and " & DATEDIF(A1,A2,"md") & " days"

For info about the DATEDIF function, look he

http://www.cpearson.com/excel/datedif.htm
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jester" wrote in message ...
|I have 2 dates and I want to calculate the difference expressed as the
| following string:
|
| (eg) 9 Years 3 months and 13 days
|
| Can anyone help?
|
| Many Thanks
|
| John - Sydney Aust
|
|


  #4  
Old March 4th, 2007, 11:28 AM posted to microsoft.public.excel.misc
Jester
external usenet poster
 
Posts: 11
Default Calculate diiference between 2 dates

Thankyou very much!!!
"Mike" wrote in message
...
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

should do the trick with the dates in A1 & A2

Mike

"Jester" wrote:

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust





  #5  
Old March 4th, 2007, 11:28 AM posted to microsoft.public.excel.misc
Jester
external usenet poster
 
Posts: 11
Default Calculate diiference between 2 dates

Thankyou very much!!!!
"Niek Otten" wrote in message
...
Hi John,

=DATEDIF(A1,A2,"y") & " Years " & DATEDIF(A1,A2,"ym") & " months and " &

DATEDIF(A1,A2,"md") & " days"

For info about the DATEDIF function, look he

http://www.cpearson.com/excel/datedif.htm
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Jester" wrote in message

...
|I have 2 dates and I want to calculate the difference expressed as the
| following string:
|
| (eg) 9 Years 3 months and 13 days
|
| Can anyone help?
|
| Many Thanks
|
| John - Sydney Aust
|
|




  #6  
Old March 4th, 2007, 11:36 AM posted to microsoft.public.excel.misc
Bill Ridgeway
external usenet poster
 
Posts: 387
Default Calculate diiference between 2 dates

I've run a quick check on this formula. It doesn't seem to include
additional leap year days.

Regards.

Bill Ridgeway
Computer Solutions

"Mike" wrote in message
...
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

should do the trick with the dates in A1 & A2

Mike

"Jester" wrote:

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust





  #7  
Old March 4th, 2007, 12:00 PM posted to microsoft.public.excel.misc
Bill Ridgeway
external usenet poster
 
Posts: 387
Default Calculate diiference between 2 dates

On reflection I suppose it wouldn't - and rightfully so. The possible
danger in calculating using year and month is that they are not a standard
length. Days on the other hand is a much more precise measure.

There is one perennial trap when calculating the period between two dates.
The answer lies in whether or not you are counting gross or net days. In a
situation of, say, the payment of a pension due in a year, the number of
days in payment is 365 (366). In a situation of, the number of days
available to do something, the number of days is 364 (365).

Regards.

Bill Ridgeway
Computer Solutions

"Bill Ridgeway" wrote in message
...
I've run a quick check on this formula. It doesn't seem to include
additional leap year days.

Regards.

Bill Ridgeway
Computer Solutions

"Mike" wrote in message
...
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

should do the trick with the dates in A1 & A2

Mike

"Jester" wrote:

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust







  #8  
Old March 4th, 2007, 12:00 PM posted to microsoft.public.excel.misc
Jester
external usenet poster
 
Posts: 11
Default Calculate diiference between 2 dates

Chhers Bill , in my case I am more than happy with what the formula does.
It meets my needs precisely


John
"Bill Ridgeway" wrote in message
...
On reflection I suppose it wouldn't - and rightfully so. The possible
danger in calculating using year and month is that they are not a standard
length. Days on the other hand is a much more precise measure.

There is one perennial trap when calculating the period between two dates.
The answer lies in whether or not you are counting gross or net days. In

a
situation of, say, the payment of a pension due in a year, the number of
days in payment is 365 (366). In a situation of, the number of days
available to do something, the number of days is 364 (365).

Regards.

Bill Ridgeway
Computer Solutions

"Bill Ridgeway" wrote in message
...
I've run a quick check on this formula. It doesn't seem to include
additional leap year days.

Regards.

Bill Ridgeway
Computer Solutions

"Mike" wrote in message
...
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " &
DATEDIF(A1,A2,"md") & " days"

should do the trick with the dates in A1 & A2

Mike

"Jester" wrote:

I have 2 dates and I want to calculate the difference expressed as the
following string:

(eg) 9 Years 3 months and 13 days

Can anyone help?

Many Thanks

John - Sydney Aust









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.