Sum race time splits

Excel times are fractional numbers. This means you can add times together with the SUM function to get total durations. However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below.

Enter times in the correct format

You must be sure that times are correctly entered in hh:mm:ss format. For example, to enter a time of 9 minutes, 3 seconds, type: 0:09:03. Excel will show the time in the formula bar as 12:09:03 AM, but will record the time properly as a decimal value.

Internally, Excel tracks times as decimal numbers, where 1 hour = 1/24, 1 minute = 1/(24*60), and 1 second = 1/(24*60*60). How Excel displays time depends on what number format is applied.

Use a suitable time format

When working with times, you must use a time format suitable to the problem. This usually means you will need to apply a custom number format to certain cells before you enter the time. This number format will control two things: (1) the format you must use to enter the time, and (2) the way the time is displayed. To apply a custom time format, follow these steps:

1. Select the cells to format.
2. Use Control + 1 (Command + 1 on a Mac) to open the Format cells dialog.
3. Select the "Number" tab.
4. Select "Custom" from the list to the left.
5. Enter the desired time format and click OK to apply.

These are the number formats used in the example shown:

mm:ss // split times
h:mm:ss // total time

If total times may exceed 24 hours, use enclose the "h" in square brackets like "[h]":

[h]:mm:ss

The square brackets tell Excel not to "reset" durations greater than 24 hours back to zero. Without the brackets, a time like 30:00:00 (30 hours) will display as 6:00:00 because Excel will reset the time to zero at 24 hours.

Tracking time with more precision

In the example above, we are tracking time down to a second, but there are cases where you will need to record time to a hundredth of a second or even a thousandth of a second (a millisecond). In that case, you will need to adjust the custom time format before entering the times. To enter time down to a hundredth of a second, use a custom time format like this:

mm:ss.00

To enter time down to a thousandth of a second (i.e. a millisecond), use a custom time format like this:

mm:ss.000

You will need to enter the seconds with a decimal value when a value is present. You can add h or [h] if needed to handle hours.