Computer Hope
Software => Computer software => Topic started by: LadyGlenProd on June 08, 2010, 02:57:36 PM
-
I need help writing a formula to subtract the end time of an event from the start time of the event.
I have tried searching functions by asking for a formula to "subtract end time from start time" and variations on this, but keep coming up with TIME(hour,minute,second) or HOUR as the option under "select a function".
I am working in Excel 2007, and I have my times listed as "8:30:00 AM" and "1:00:00 PM" which is the time format as provided by Excel. Here is an example of my formula:
(cell A1 reads) 8:30:00 AM
(cell A2 reads) 1:00:00 PM
Formula: =(A1-A2)
Answer when requested as time: ########################################
Answer when requested as number: -0.19
It seems so simple, but I am so frustrated! I need it to build an excel spreadsheet for my boss.
Thank you!
-
I need help writing a formula to subtract the end time of an event from the start time of the event.
I have tried searching functions by asking for a formula to "subtract end time from start time" and variations on this, but keep coming up with TIME(hour,minute,second) or HOUR as the option under "select a function".
I am working in Excel 2007, and I have my times listed as "8:30:00 AM" and "1:00:00 PM" which is the time format as provided by Excel. Here is an example of my formula:
(cell A1 reads) 8:30:00 AM
(cell A2 reads) 1:00:00 PM
Formula: =(A1-A2)
Answer when requested as time: ########################################
Answer when requested as number: -0.19
Formula: =ABS(A1-A2)
Format: Formula as Time, in type 13:30, i.e. no AM or PM
(Formula cell): 4:30
-
yes, type your times using the 24 hr military format and you're good to go.
-
Alternately, (A2-A1)*24 gives you the difference, in decimal form. (In other words, 4.5 hours).
What you're getting with A2-A1 -- that is, 0.19 (rather, 0.1875) is the number of DAYS between the two numbers. Since it's less than a day, it gives you a decimal. Multiplying by 24 hours gives you the number of hours.