Difference between two date/time values

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

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
 
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")
 
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
 
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")
Click to expand...

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")
 
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
 
Back
Top