How to average a range of times
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Find the mean of clock times or durations — average response time, average shift length.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =AVERAGE(A2:A6) | Format the result cell as a time (h:mm or [h]:mm). AVERAGE works on times because they're numbers. |
| Google Sheets | =AVERAGE(A2:A6) | Identical. |
| LibreOffice Calc | =AVERAGE(A2:A6) | Identical. |
How it works
Times are stored as fractions of a day, so AVERAGE handles them like any other number: the mean of 9:00, 10:00 and 11:00 is 10:00. The only catch is display — the raw result is a fraction, so format the cell as a time (h:mm), or [h]:mm if you're averaging durations that might exceed 24 hours. To average elapsed durations for pay or SLAs, multiply the result by 24 for decimal hours. Blank cells are ignored automatically; text-formatted times won't average until converted with TIMEVALUE.
Verified, not just documented
We ran =TEXT(AVERAGE(TIME(9,0,0),TIME(10,0,0),TIME(11,0,0)),"H:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 10:00 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 10:00 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
AVERAGE — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to average a range that contains errors
- How to average the top N scores (drop the lowest)
- How to average the last N values in a growing column
- How to calculate a moving average
- How to calculate a z-score