Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Create Excel Formula to subtract end time from start time  (Read 11156 times)

0 Members and 1 Guest are viewing this topic.

LadyGlenProd

  • Guest
Create Excel Formula to subtract end time from start time
« 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!

Computer_Commando



    Hacker
  • Thanked: 485
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 7
Re: Create Excel Formula to subtract end time from start time
« Reply #1 on: June 08, 2010, 05:13:13 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

2x3i5x



    Expert

    Thanked: 116
  • Computer: Specs
  • Experience: Familiar
  • OS: Windows 8
Re: Create Excel Formula to subtract end time from start time
« Reply #2 on: June 08, 2010, 06:15:06 PM »
yes, type your times using the 24 hr military format and you're good to go.

rjbinney



    Apprentice

  • Disarmingly Good-looking
    Re: Create Excel Formula to subtract end time from start time
    « Reply #3 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.
    Dan: You're gonna need to get someone to fix my computer.                     Kim: What's wrong with it?                     Dan: It's in several pieces on my floor.