1.

Solve : Create Excel Formula to subtract end time from start time?

Answer»

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!Quote from: 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
Formula: =ABS(A1-A2)
Format: Formula as Time, in type 13:30, i.e. no AM or PM
(Formula cell): 4:30yes, 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.


Discussion

No Comment Found

Related InterviewSolutions