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 18561 times)

0 Members and 1 Guest are viewing this topic.

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

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: 490
• Certifications: List
• Computer: Specs
• Experience: Expert
• OS: Other
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:

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
Windows10

2x3i5x

Expert

Thanked: 116
• Computer: Specs
• Experience: Familiar
• OS: Other
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
• Experience: Beginner
• OS: Other
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.