Calculate the difference betwen two columns -two dates
 

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

Calculate the difference betwen two columns -two dates
 

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

On Mar 24, 3:36*pm, Chi wrote:
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

Calculate the difference betwen two columns -two dates
 

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

"Chi" wrote:

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

Calculate the difference betwen two columns -two dates
 

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

"Chi" wrote:

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