Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate number of codes betwen dates | Excel Worksheet Functions | |||
Calculate difference in dates | Excel Discussion (Misc queries) | |||
How do I calculate the difference between 2 dates (m,d,y) ? | Excel Worksheet Functions | |||
Calculate Difference b/e 2 dates. | New Users to Excel | |||
Calculate difference between two dates | Excel Worksheet Functions |