Adding up time in Excel

I recently wanted to add up a the times of some videos (to use in last week's post - see here).

I thought that all I would need to do was to enter the time using the US full colon break points, as shown in the image. Excel would then simply recognise the format, and sum the times.

But no. It is more complicated than that (of course it is).

When we enter 00:00:29, Excel assumes we are wanting to tell the clock time, not consider time duration, and formats the cell as "h:mm:ss AM/PM", creating a value of 29 seconds past midnight. What happens when we total the numbers illustrated is that we get a sum of "0.119837963". This is not entirely useful (!).

What we need to do is to go into the cell formatting, to select "Custom" on the format options, then to key in a new custom format, as follows:

hh:mm:ss

Then we can sum our times, and Excel will return a total which makes sense: that of two hours, 52 minutes, and some seconds.

Too easy... but only when we know how.

Sam