Time manipulation

[bhuettemann]

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?

 

[NBVC]

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

 

[cfree36]

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.

 

[Greg Truby]

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 ³/₄ 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^rd 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