Calculate the difference betwen two columns -two dates

[Chi]

Hi,

I would like to calculate the hours between two columns: First Contact (G)
and Appointment (H) with (m/d/yyyy) h:m AM/PM) format.

First Contact (G) Appointment (H) (J) (= H-G)
1/2/2010 10:00 AM 1/3/2010 10:00 AM 0:00
1/1/2010 8:00 AM 1/1/2010 9:00 AM 1:00
1/1/2010 8:15 AM 1/1/2010 8:30 AM 0:15

I added the formula (=H-G) on column J to calculate the difference between
(H) and (G) However, I don't understand why (1/2/2010 10:00 AM) (1/3/2010
10:00 AM)=0:00. It should be 12 hours.( the first line of my example). The
last two lines are correct, but the first line is wrong. Please help.
Thanks
Chi

On the column

 

[Pete_UK]

That first answer is actually 24 hours difference, which wraps as 1
day and this is not shown because of the way the cell is formatted.
Highlight column J, and then apply a Custom format to those cells of:

[h]:mm

The square brackets around the h prevents the wrapping of 24 hours
into days, so you should see 24:00.

Hope this helps.

Pete

 

[Reg]

if you press F1 and search for Hours you will find the help text:


1
2
A B
Start time End time
6/9/2007 10:35 AM 6/10/2007 3:30 PM
Formula Description (Result)
=INT((B2-A2)*24) Total hours between two times (28)
=(B2-A2)*1440 Total minutes between two times (1735)
=(B2-A2)*86400 Total seconds between two times (104100)
=HOUR(B2-A2) Hours between two times, when the difference does not exceed
24. (4)
=MINUTE(B2-A2) Minutes between two times, when the difference does not
exceed 60. (55)
=SECOND(B2-A2) Seconds between two times, when the difference does not
exceed 60. (0

RegMigrant

 

[stumac]

Hi Chi, surely the first line of your example should return 24:00?

if it is over 23:59 excel will start at 00:00 again, try changing the format
of your cells to [hh]:mm

Hth
Stu