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 » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Difference between two date/time values



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 04:30 PM posted to microsoft.public.excel.worksheet.functions
Raj[_4_]
external usenet poster
 
Posts: 27
Default Difference between two date/time values

Hi,

Cell A1 has 21-04-10 07:00 PM
Cell A2 has 06-06-10 07:52 AM
(the dates are in the dd-mm-yy format)

Cell A3 has the formula =A2-A1 for showing the differences in days and
time and is formatted with the custom format:
dd h:mm:ss

The display in cell A3 is 14 12:52:00. While the difference in hours
is okay, the difference in days should be more than 14 as there is are
31 days of May between the two dates. Is there another format to show
the correct difference in days or should be done some other way?
Please help.

Thanks in advance.

Regards,
Raj


  #2  
Old May 31st, 2010, 06:16 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Difference between two date/time values

Do you really need the seconds? Your sample entries only show date h:mm.

If using a custom format of dd h:mm:ss the max *displayed* number of days
will be the number of days in the *current month*.

So, you can't use this format if the number of days are expected to be 31.
You'd have to use a formula.

Assuming the date/time in A2 will *always* be = the date/time in A1.

=MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm")

--
Biff
Microsoft Excel MVP


"Raj" wrote in message
...
Hi,

Cell A1 has 21-04-10 07:00 PM
Cell A2 has 06-06-10 07:52 AM
(the dates are in the dd-mm-yy format)

Cell A3 has the formula =A2-A1 for showing the differences in days and
time and is formatted with the custom format:
dd h:mm:ss

The display in cell A3 is 14 12:52:00. While the difference in hours
is okay, the difference in days should be more than 14 as there is are
31 days of May between the two dates. Is there another format to show
the correct difference in days or should be done some other way?
Please help.

Thanks in advance.

Regards,
Raj




  #3  
Old May 31st, 2010, 06:18 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Difference between two date/time values

Formatted as a number =A2-A1 is 45.54

45.54 is the serial number for Tuesday, February 14, 1900 12:52:00PM

That's why you get 14 12:52:00

Enter this formula in A3

=DATEDIF(A1,A2,"d")-1 & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm""
minutes ""ss"" seconds""")

Returns 45 days 12 hours 52 minutes 00 seconds


Gord Dibben MS Excel MVP



On Mon, 31 May 2010 08:30:48 -0700 (PDT), Raj wrote:

Hi,

Cell A1 has 21-04-10 07:00 PM
Cell A2 has 06-06-10 07:52 AM
(the dates are in the dd-mm-yy format)

Cell A3 has the formula =A2-A1 for showing the differences in days and
time and is formatted with the custom format:
dd h:mm:ss

The display in cell A3 is 14 12:52:00. While the difference in hours
is okay, the difference in days should be more than 14 as there is are
31 days of May between the two dates. Is there another format to show
the correct difference in days or should be done some other way?
Please help.

Thanks in advance.

Regards,
Raj


  #4  
Old May 31st, 2010, 08:28 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Difference between two date/time values

Doesn't that formula go wrong if the time in A1 is earlier than the time in
A2, Gord?

Isn't it easier not to use DATEDIF, and just to use
=INT(A2-A1) & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" ?
--
David Biddulph


"Gord Dibben" gorddibbATshawDOTca wrote in message
...
Formatted as a number =A2-A1 is 45.54

45.54 is the serial number for Tuesday, February 14, 1900 12:52:00PM

That's why you get 14 12:52:00

Enter this formula in A3

=DATEDIF(A1,A2,"d")-1 & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm""
minutes ""ss"" seconds""")

Returns 45 days 12 hours 52 minutes 00 seconds


Gord Dibben MS Excel MVP



On Mon, 31 May 2010 08:30:48 -0700 (PDT), Raj wrote:

Hi,

Cell A1 has 21-04-10 07:00 PM
Cell A2 has 06-06-10 07:52 AM
(the dates are in the dd-mm-yy format)

Cell A3 has the formula =A2-A1 for showing the differences in days and
time and is formatted with the custom format:
dd h:mm:ss

The display in cell A3 is 14 12:52:00. While the difference in hours
is okay, the difference in days should be more than 14 as there is are
31 days of May between the two dates. Is there another format to show
the correct difference in days or should be done some other way?
Please help.

Thanks in advance.

Regards,
Raj



  #5  
Old May 31st, 2010, 10:47 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Difference between two date/time values

Tweak...

Assuming the date/time in A2 will *always* be = the date/time in A1.
=MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm")


If the date/time in A2 will *always* be = the date/time in A1 then we don't
need the MAX function:

=INT(A2-A1)&" "&TEXT(MOD(A2-A1,1),"h:mm")


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Do you really need the seconds? Your sample entries only show date h:mm.

If using a custom format of dd h:mm:ss the max *displayed* number of days
will be the number of days in the *current month*.

So, you can't use this format if the number of days are expected to be
31. You'd have to use a formula.


Assuming the date/time in A2 will *always* be = the date/time in A1.

=MAX(0,INT(A2-A1))&" "&TEXT(MOD(A2-A1,1),"h:mm")

--
Biff
Microsoft Excel MVP


"Raj" wrote in message
...
Hi,

Cell A1 has 21-04-10 07:00 PM
Cell A2 has 06-06-10 07:52 AM
(the dates are in the dd-mm-yy format)

Cell A3 has the formula =A2-A1 for showing the differences in days and
time and is formatted with the custom format:
dd h:mm:ss

The display in cell A3 is 14 12:52:00. While the difference in hours
is okay, the difference in days should be more than 14 as there is are
31 days of May between the two dates. Is there another format to show
the correct difference in days or should be done some other way?
Please help.

Thanks in advance.

Regards,
Raj






  #6  
Old May 31st, 2010, 11:07 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Difference between two date/time values

Yes.

After seeing Biff's reply I realized there is an easier and more accurate
method.

Your suggestion is basically same as his. although as written, your formula
return an error due to the extra " after mm

My final try.............

=INT(A2-A1)&" days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" minutes""")



Gord


On Mon, 31 May 2010 20:28:20 +0100, "David Biddulph" groups [at]
biddulph.org.uk wrote:

Doesn't that formula go wrong if the time in A1 is earlier than the time in
A2, Gord?

Isn't it easier not to use DATEDIF, and just to use
=INT(A2-A1) & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" ?


  #7  
Old June 1st, 2010, 12:58 PM posted to microsoft.public.excel.worksheet.functions
Raj[_4_]
external usenet poster
 
Posts: 27
Default Difference between two date/time values

Thanks to All; these were the insights I was looking for.

Regards.
Raj


On Jun 1, 3:07*am, Gord Dibben gorddibbATshawDOTca wrote:
Yes.

After seeing Biff's reply I realized there is an easier and more accurate
method.

Your suggestion is basically same as his. although as written, your formula
return an error due to the extra " *after mm

My final try.............

=INT(A2-A1)&" days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" minutes""")

Gord

On Mon, 31 May 2010 20:28:20 +0100, "David Biddulph" groups [at]

biddulph.org.uk wrote:
Doesn't that formula go wrong if the time in A1 is earlier than the time in
A2, Gord?


Isn't it easier not to use DATEDIF, and just to use
=INT(A2-A1) & " days "&TEXT(MOD(A2-A1,1),"hh "" hours ""mm"" ?


 




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 03:28 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.