Add or Sub Time

[amerifax]

I have two cells formated to TIME 1:30 PM.
I have a column that has start times. Another with finish times.

A1 = Start
B1 = Finish

My natural thought is =a1-b1 would give me my total time for the day.
Wrong I am.

What do I need to do?

Bob

 

[stopher]

Assuming you have your the data type date/time in your table then that's pretty much what you need to do.

Time is stored as a decimal representing a percentage of a day e.g. 0.5=12:00 or 12 hours, 0.75=18:00 or 18 hours etc.

So 0.75-0.5=0.25 =>6 hours (or 06:00 if you were to interpret it that way)

All you need to do is format the result as time.

The only problem you face is when you are summing time. Suppose you summed time and it came to 30 hours (1.25) then if you format this as time then it will only show as 6 hours because it only shows the time part (the bit after the decimal point). This problem can be dealt with depending on how it is intended to be used.

hth
Chris

 

[HaHoBe]

Hi, Bob,

shouldn´t it be Finsih Time - Start Time? Please mind that Excel in normal mode cannot display negative times (only if you change Tools/Options/Calculate to 1904-Dates which will affect all dates esp. in linked workbooks). Format the cell(s) for the sum as [h]:mm.

Ciao,
Holger