Adding time

[Mike]

I am trying to add times such as 28:00,35:00,12:00 and I keep getting zero
for the sum, could someone please give me a formula or format for this
problem.

Thanks,

Mike

 

[OssieMac]

Hi Mike,

I would suggest that your times are entered as text and not as formatted
times.

Select the column and then set the number format to custom format to [hh]:mm

Then select any blank cell on the worksheet and enter 1 and then copy that
cell and then select all of your times and Paste Special -> Multiply to force
the entries from text to time. (Multiplying by 1 does not change the value)

Note that the square brackets around the hh forces it to remain in hours and
not start the time again after 24hrs.

 

[Chip Pearson]

Times are just numbers, a fraction of a 24 hour day (6:00 = 0.25,
12:00 = 0.5, 18:00 = 0.75 etc), so you can just add them up normally.
Format the result cell with a custom number format of [hh]:mm. The
square brackets prevent Excel from "rolling over" the hours at 24:00.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]