Computer Hope

Software => Computer software => Topic started by: LadyGlenProd on June 08, 2010, 02:57:36 PM

Title: Create Excel Formula to subtract end time from start time
Post 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!

Title: Re: Create Excel Formula to subtract end time from start time
Post by: Computer_Commando on June 08, 2010, 05:13:13 PM

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:30

Title: Re: Create Excel Formula to subtract end time from start time
Post by: 2x3i5x on June 08, 2010, 06:15:06 PM

yes, type your times using the 24 hr military format and you're good to go.

Title: Re: Create Excel Formula to subtract end time from start time
Post by: rjbinney on June 09, 2010, 02:02:16 PM

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.