Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to reference named range different sheet in Excel
  • Vlookup Examples in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to use Excel ROW Function
  • Last row number in range

Data Analysis Examples

  • Remove Duplicates Example in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Number and Text Filters Examples in Excel
  • How To Create Frequency Distribution in Excel
  • Reverse List in Excel

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only
  • Excel Data validation allow uppercase only
  • Excel Data validation only dates between
  • Excel Data validation date in specific year

Sum race time splits in Excel

by

If you need to add up (sum) up race time splits that are some combination of hours, minutes, and seconds, you can simply use the SUM function.

However, you must take care to enter times with the right syntax and use a suitable time format to display results, as explained below.

Formula

=SUM(range)

Explanation

The formula in cell H5 is:

=SUM(C5:G5)

Enter times in 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

Worked Example:   Advanced Number Formats in Excel

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.

Worked Example:   How to Sum Range with Errors in Excel

Use a suitable time format

When working with times, you must take care to use a time format that makes sense to display times that represent durations.

To access and apply custom time formats, select the cell(s), then use Control + 1 (Command + 1 on a Mac), then Number > Custom.

Worked Example:   How To Use AGGREGATE function to sum a range with errors in Excel

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 this format:

[h]:mm:ss

The square bracket syntax tells Excel not to “roll over” times greater than 24 hours.

Post navigation

Previous Post:

Two ways to sum time over 30 minutes in Excel

Next Post:

Pad week numbers with zeros in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to strip protocol and trailing slash from URL in Excel
  • How to remove trailing slash from url in Excel
  • How to add a line break with a formula while joining text strings in Excel
  • Excel MAX, MATCH and ADDRESS function illustration
  • How to extract name from email address in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • How to get Weekdays, Working days between Two Dates in Excel
  • Sum through n months in Excel
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Next biweekly payday from date in Excel
  • How to calculate Net Present Value (NPV) in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • Future value vs. Present value examples in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles