To average durations in Excel, enter them as time values, use the AVERAGE function, and format the result as [h]:mm. For example, if durations are in A2:A6, enter =AVERAGE(A2:A6) in another cell. The custom format shows the answer in hours and minutes, including totals over 24 hours. This guide explains how to prepare the values, apply the format, and fix common problems.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The steps are similar in Excel for Mac and Excel for the web, though some dialogs may look different.
- A column of durations, such as shift lengths, task times, or travel times. Keep the values in a consistent format, such as hours and minutes.
- A blank cell for the average. If your values represent clock times rather than elapsed durations, see the note about times crossing midnight in the steps below.
Step-by-Step
- Enter each duration in a cell. Put a label such as Duration in A1, then enter the values in A2:A6, one per row. For example, type
1:15for one hour and 15 minutes,2:30for two hours and 30 minutes, and0:45for 45 minutes. Excel stores recognized time entries as numbers representing fractions of a day, so they can be averaged. Use the same units and entry style throughout the list. For a duration longer than a day, enter the hours and minutes and plan to use the elapsed-time format in step 4. - Check that Excel recognized the entries as numbers. Select a few duration cells and look at the formula bar. A value may display as a time even though Excel stores it numerically. A quick clue is alignment: numbers generally align to the right by default, while text generally aligns to the left. Alignment can be changed manually, so it is only a clue. If entries came from a website, CSV, or another system, they may be text; the troubleshooting section covers conversion.
- Choose the cell where the average should appear. Click a blank cell below the list or in a summary area. With durations in A2:A6, the formula is
=AVERAGE(A2:A6). Press Enter. AVERAGE returns the arithmetic mean of the numeric cells and ignores blank cells. For the sample values 1:15, 2:30, 0:45, 3:00, and 2:00, the total is 9 hours 30 minutes; divided by five, the average is 1 hour 54 minutes. - Format the result to show hours and minutes. Select the average cell and press Ctrl+1 on Windows to open Format Cells. On the Number tab, choose Custom, type
[h]:mmin the Type box, and choose OK. The square brackets around h tell Excel to display total elapsed hours instead of resetting the hour count after 24. The same format is useful for the original duration cells, so select those cells too if you want their display to be consistent. On Mac, use Command+1 to open Format Cells; in Excel for the web, use the number-format controls on the Home tab and look for the custom format option. - Check the result against the sample arithmetic. In the example, the five values add to 9:30, and 9:30 divided by 5 equals 1:54. If your sheet does not show that result, verify that all five entries are numeric, that the intended range is included in the formula, and that the formula cell uses an hours-and-minutes format. A cell showing a decimal such as 0.0791667 may contain the correct average but still need formatting.
- Decide whether you need a decimal-hour answer as well. A time value is stored as a fraction of a 24-hour day. With durations in A2:A6, use
=AVERAGE(A2:A6)*24to return the average as decimal hours; for the sample, the result is 1.9 hours. Format that cell as Number rather than as a time. This is useful for reports that require decimal hours, but do not confuse 1.9 hours with 1 hour 9 minutes: 0.9 hour is 54 minutes. - Extend the calculation when you add more rows. If new durations will be added regularly, you can include a larger range in the formula, such as A2:A100, or convert the list into an Excel table with Insert > Table. A formula using a table column can expand with new rows. If using a fixed range, make sure it includes every entry and does not unintentionally include totals or other unrelated numbers.
Clock times are different from durations. A duration such as 2:30 means two hours and 30 minutes. A clock time such as 2:30 means a particular time of day. Averaging clock times that span midnight with a plain AVERAGE formula can give a misleading result: 11:50 PM and 12:10 AM appear to average to noon if Excel treats them as times on the same date. If you have actual date-and-time records, retain the date with each time and average the complete date-time values; format the result to show the time you need. If you have only clock times and they cross midnight, determine the correct day or use a method designed for circular time-of-day data rather than treating them as ordinary durations.
Common Mistakes
- Using h:mm instead of [h]:mm for elapsed time. The unbracketed format starts the hour display over after 24 hours. For example, a total of 27 hours can appear as 3:00. Apply
[h]:mmto show 27:00. This issue affects display, not the underlying calculation. - Entering minutes as though they were decimal fractions. Typing 1.30 does not mean 1 hour and 30 minutes; it means the decimal number 1.3. Enter a time value such as 1:30 for one hour and 30 minutes. If your source lists hours as decimals, convert them consistently before averaging.
- Including a total row in the average range. If a column contains individual durations followed by their sum, averaging the whole column counts the sum as another observation and inflates the result. Select only the rows that contain individual entries.
- Assuming blanks and zeroes mean the same thing. A blank cell is ignored by AVERAGE, while a numeric zero is included. If a missing entry should not count, leave it blank rather than entering 0:00. If zero duration is a real result, keep it in the calculation.
- Confusing time of day with elapsed time. A shift from 10:00 PM to 6:00 AM lasts eight hours, but subtracting the clock values without accounting for the date can produce an invalid or negative-looking result. Store complete dates and times, or calculate each duration with a method that handles the midnight crossing before averaging.
Tips
- Use a clear column heading such as Duration (h:mm) so people know that 2:30 means two and a half hours, not a time of day.
- Apply the same custom format to the input values and result. This makes it easier to compare the average with the entries and helps prevent a correct value from looking like a decimal fraction of a day.
- Keep the original records unchanged if you need both a formatted duration and a decimal-hour value. Put the two calculations in separate summary cells, and label them clearly.
- For average minutes rather than average hours and minutes, multiply the average time by 1,440, the number of minutes in a day. With durations in A2:A6, enter
=AVERAGE(A2:A6)*1440; the sample returns 114 minutes. Format the result as a number. - Use extra decimal places only when your reporting needs them. A displayed average of 1:54 is exact for the sample, but other averages can include seconds or fractions of a minute. Choose a display format such as
[h]:mm:ssif seconds matter, or round only when the business rule calls for it.
Troubleshooting
- The result displays as a decimal. The calculation may be correct: Excel stores time as a fraction of a day. Select the result, open Format Cells with Ctrl+1 on Windows (Command+1 on Mac), and apply the Custom format
[h]:mm. In Excel for the web, use the Home tab’s number-format controls to choose or create a suitable format. - The result shows 0:00 or seems much too small. Check whether the source entries are text rather than numeric time values. If they are imported text, select the affected cells and try Data > Text to Columns, then complete the wizard with the appropriate options so Excel can interpret the values. Check the result afterward, because separators and date settings can affect how imported values are recognized. You can also test a single value in a blank cell by changing its number format; a value that remains literal text needs conversion before averaging.
- The displayed total loses hours after 24. Apply
[h]:mmrather thanh:mmto the output cell. Square brackets are required for cumulative elapsed hours. - The formula returns an error. Confirm that the formula begins with an equals sign, the cell range is correct, and no error value is present among the input cells. AVERAGE can calculate from numeric cells while ignoring text and blanks, but an error in the referenced range can carry through to the result.
- The average seems mathematically wrong. Confirm you are averaging durations rather than clock times, count the numeric entries, and check whether zeroes or a total row are included. For a quick manual check, add the durations and divide by the number of entries. In the five-value example, 9:30 divided by 5 must equal 1:54.
- You see ####### in the cell. The column may be too narrow to display the formatted value. Widen the column by dragging the boundary at the right of its heading, or double-click that boundary to AutoFit it.
Frequently Asked Questions
Can Excel average hours and minutes directly?
Yes. Enter recognized durations as time values and use AVERAGE on their cells. Apply [h]:mm to display the result as elapsed hours and minutes.
Why does Excel show my average as a decimal?
Excel stores time as a fraction of a day. The decimal is the underlying value; apply the custom format [h]:mm to display hours and minutes, or multiply by 24 if you need decimal hours.
How do I average durations longer than 24 hours?
Use the same AVERAGE formula, then format the result as [h]:mm. The bracketed hour code displays accumulated hours instead of wrapping at 24.
Does AVERAGE count blank cells?
No. AVERAGE ignores blank cells in a referenced range, but it includes numeric zeroes. Decide whether a missing record should be blank or a genuine zero before calculating.
How do I average times that cross midnight?
Do not treat clock readings on different sides of midnight as ordinary durations. Use full date-and-time records so Excel can distinguish which day each reading belongs to, or first convert the readings into the correct elapsed durations. A plain average of clock times alone can be misleading.
Can I show the average as total minutes?
Yes. Multiply the average time value by 1,440 and format the result as a number. For example, an average of 1:54 is 114 minutes.
Related
- Free Excel Meal Plan Template: Build a Weekly Planner
- Fixing Broken Links in Excel: Find and Repair Them
- Find Same Names in Two Excel Columns
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Split Text Into Columns in Google Sheets EasilyTo split text in Google Sheets, select the cells, choose Data > Split text to columns, then pick…
- How to Bold Subtotal Rows in ExcelTo bold subtotal rows created with Excel’s Subtotal command, collapse the outline to level 2, select the data…
- How to Delete Blank Cells in Excel Without Losing DataTo delete blank cells in Excel, select the range, choose Home > Find & Select > Go To…
- Smartsheet DataTable Explained: Uses, Setup, and LimitsA Smartsheet DataTable is a centrally managed data store that can feed selected records into Smartsheet sheets, helping…
- Excel Freeze Panes in the Middle of a Sheet: How It WorksYes. In Excel for Microsoft 365 on Windows, select the cell immediately below and to the right of…
- Extract Unique Values in Google Sheets: A Quick GuideTo extract a live list of distinct values in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.