Time manipulation

bhuettemann

New Member
Joined
Nov 23, 2005
Messages
1
Is it possible to add/subtract time when input as am & pm. Lets say I want to subtract the start time of job from the end time: A1 = 10:00am & B1 = 2:00pm. If I enter into C1 =SUM(A1-B1), I get #VALUE! Can I manipulate these in thier current format? If not how can I convert them to something usable?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Make sure there is a space between the time and the AM or PM within the cell.

You don't need SUM function, just use =B1-A1
 
Upvote 0

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
You can. Excel uses a 24 hour clock... type in 10:00 for 10:00 AM... don't type the AM but rather format the cell by time and select what you want to see.
This way you can perform formulas on the times.
 
Upvote 0

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Welcome to the board!

As NBVC has already indicated, the call to SUM() is superfluous. You merely need enter the formula referencing the cells as though they were variables in an equation.

You should know that while Excel displays times in the cell as 1:00 pm and 1:00:00 pm in the formula bar, internally Excel considers time as "part of a day". So noon is midday is 0.5; six o'clock in the morning is a quarter of the way through the day, so Excel considers it to be 0.25; 6:00 pm (or 18:00) is <sup>3</sup>/<sub>4</sub> of the way through the day so it's 0.75. If you add times and go past one day, Excel shows you "the remainder". Add 1:00 pm (13:00) and 2:00 pm (14:00); you get "3:00 am". If you change the cell's format to [hh]:mm then you'll see 27:00. As for subtraction, your error was not caused by using an extraneous SUM(). It was caused by trying to go below zero. Excel does not much like "negative time". This can come into play when you have 3<sup>rd</sup> shift start times, i.e. shift starts at 8:00 pm and ends at 4:00 am. Since 8:00 pm = 0.83333 days and 4:00 am = 0.166666 days; you cannot subtract end-start directly. You need to realize that the 4:00 am is 4:00 am the next day and make it 1.16666 by adding 1 to the end time.

HTH
 
Upvote 0

Forum statistics

Threads
1,195,748
Messages
6,011,421
Members
441,614
Latest member
TiaGtz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top