To calculate average hours in Excel, enter each duration as a time value, then use =AVERAGE(B2:B6) for durations in B2:B6. Format the result as [h]:mm so it displays as hours and minutes, including totals or averages above 24 hours. This method works for shift lengths, task times, and other elapsed durations.
What You’ll Need
- A Microsoft Excel workbook with one duration per cell in a single column or row.
- Excel for Microsoft 365 on Windows, or a recent Excel version with the AVERAGE function. The steps and function also work in Excel for Mac and Excel for the web, although some keyboard shortcuts and menus differ.
- A clear definition of what you want to average: elapsed hours, such as the length of a shift, or clock times, such as when shifts begin. These are different kinds of data and should not be averaged the same way.
Step-by-Step
- Set up a column for durations. Add a descriptive heading such as Hours Worked in A1, then put one duration in each cell below it. For example, enter 7:30 for seven hours and thirty minutes, 8:00 for eight hours, and 6:45 for six hours and forty-five minutes. Excel stores recognized times as fractions of a day, so these values can be averaged as numbers. Do not type words such as “hours” in the same cells as the values; text in a duration cell can prevent Excel from using it in the calculation.
- Enter the duration values consistently. Use hours and minutes in each cell, for example 9:15 for nine hours and fifteen minutes. A colon is the usual separator for time values. If you are entering durations that can exceed 24 hours, select the input cells, open Format Cells, choose Number > Custom, and enter
[h]:mmas the format before entering the values. The square brackets make Excel display total hours rather than rolling over to a new day. For ordinary shifts shorter than 24 hours, you can enter the values first and apply the format afterward. - Choose the cells to average. Decide which entries belong in the calculation. In the example, the five durations are in B2:B6, with no header inside the range. The AVERAGE function ignores blank cells, but it includes zeros. Check that the range contains the records you intend to compare and does not include a total, a label, or an unrelated number.
- Enter the average formula in an empty cell. With the five example durations in B2:B6, enter
=AVERAGE(B2:B6)in B7 or another empty cell. Press Enter. The formula returns the arithmetic mean of the numeric durations in that range. For example, if B2:B6 contain 7:30, 8:00, 6:45, 9:15, and 8:30, their sum is 40 hours and the average is 8:00. Use your own range in place of B2:B6 when your data occupies different cells. - Format the result as elapsed hours. Select the formula cell and press Ctrl+1 on Windows to open Format Cells. Choose Number > Custom, type
[h]:mmin the Type box, and select OK. The result should show hours and minutes, such as 8:00. On a Mac, select the cell and press Command+1 to open Format Cells; choose the equivalent Custom format option. In Excel for the web, use Home > Number Format and choose More Number Formats if the custom format option is available in your current interface. The formula value does not change when you change its display format. - Check the result against the records. An average should fall between the smallest and largest valid durations, unless the range includes an unintended value. For the example, 8:00 is reasonable because the five durations average exactly 40 total hours divided by 5 entries. If the result looks like a fraction, a date, or a clock time, inspect its number format before changing the formula.
- Adjust the range when you add records. If new entries are added below the original range, update the formula to include them. For example, with durations now in B2:B10, use
=AVERAGE(B2:B10). Alternatively, convert the data into an Excel table using Insert > Table, then add new records in the table so the range can expand more easily. Confirm that the formula includes the new rows before relying on the updated result.
Average decimal hours instead
If your durations are already stored as decimal numbers, such as 7.5 for seven and a half hours, AVERAGE works directly on those numbers. With decimal-hour values in B2:B6, enter =AVERAGE(B2:B6). Format the result as Number to show a decimal, such as 8.0. Do not apply [h]:mm directly to a decimal-hour result: Excel interprets a numeric value such as 8 as eight days, not eight hours. To show the average decimal hours as hours and minutes, divide by 24 in the formula. With decimal-hour values in B2:B6, enter =AVERAGE(B2:B6)/24 in an empty cell and format that cell as [h]:mm. If the average is 8 decimal hours, the displayed result is 8:00.
Calculate durations from start and end times
If your records contain clock-in and clock-out times rather than completed durations, calculate each elapsed duration first, then average that duration column. For a start time in B2 and an end time in C2, enter =MOD(C2-B2,1) in D2. This returns the elapsed time and also handles a shift that crosses midnight, such as 10:00 PM to 6:00 AM. Format D2 as [h]:mm, then fill the formula down for the other rows. With the resulting durations in D2:D6, enter =AVERAGE(D2:D6) in an empty cell and format the average as [h]:mm. The MOD calculation assumes each shift lasts less than 24 hours; a single start and end time cannot distinguish a shift longer than a day without additional information.
Common Mistakes
- Using a clock-time format for a duration. Excel may display a sum or average using a clock-style format that wraps after 24 hours. Apply
[h]:mmto show accumulated or elapsed hours. Formatting changes how a value appears, not the underlying calculation. - Mixing decimal hours and time entries. The value 7.5 means seven and a half days to Excel if treated as a time serial, while 7:30 represents seven and a half hours. Keep the data in one system, or convert decimal hours to time by dividing by 24 before applying an hours-and-minutes format.
- Including zeros unintentionally. AVERAGE ignores blank cells but includes cells containing 0. If a zero means “no shift recorded” rather than a genuine zero-hour result, remove or correct that entry before averaging. Do not remove valid zero values when they belong in the calculation.
- Averaging start times instead of work durations. An average start time answers when a typical shift begins; it does not tell you the average length of a shift. Calculate end time minus start time for each record first, particularly when shifts cross midnight.
- Including totals in the average range. If a weekly total sits below the daily durations, including it counts that total as another observation and skews the result. Keep summary cells outside the range used for individual records.
Tips
- Use a heading that says what each number measures, such as Shift Duration (hours:minutes) or Decimal Hours. This helps prevent later confusion when someone else uses the workbook.
- Keep original clock-in and clock-out times as well as the calculated duration when you need an auditable record. A separate duration column makes it easier to spot an incorrect shift without changing the source times.
- For a result that must be entered into another system as a decimal, use decimal-hour values or convert the duration result by multiplying the Excel time by 24. Format the converted result as Number and choose the decimal places that suit your use.
- Use the same units and time-entry conventions for every record. A value entered as 8:30 means eight hours and thirty minutes, not 8.30 decimal hours; those quantities differ.
- If you want the average for a subset, make a separate range for that group or use a suitable conditional calculation. Do not quietly remove records from the range without documenting why they were excluded.
- For a quick sense check, add the durations and divide by the count of included records. The average should match that total divided by the number of entries, with blanks excluded and zeros counted.
Troubleshooting
The result shows a decimal such as 0.3333. Excel is displaying the stored fraction of a day in a general number format. Select the result cell, open Format Cells, and apply the custom format [h]:mm. A value of 0.3333 days is approximately eight hours, so it should display close to 8:00.
The result displays as a time of day or appears to reset after 24 hours. Apply the custom format [h]:mm to the result. A format such as h:mm can show a clock-style hour and wrap after a full day; the square brackets in [h] display cumulative hours. If the result still seems wrong, confirm that the source cells contain durations rather than dates or unrelated time values.
The formula returns #DIV/0!. AVERAGE returns this error when it finds no numeric values to average. Check that the referenced range is correct and that the entries are actual Excel numbers or times, not text that merely looks like a time. Re-enter a sample value using a colon, such as 8:00, and see whether changing its cell format changes its appearance. If the values were imported, they may need to be converted from text into numeric time values before the formula can use them.
The result is lower than expected. Inspect the range for zeros, short entries, or a total mistakenly included as an individual record. Check that entries such as 7:30 were not entered as 7.30, and make sure all durations use the same units. AVERAGE ignores text and empty cells, so a result based on fewer numeric entries than expected can also be misleading.
An overnight shift produces a negative or incorrect duration. If start and end times are in separate columns, use the MOD method shown above, then format the duration cells as [h]:mm. Check that the start and end cells contain time values and that the formula refers to the correct row. For shifts that can run longer than 24 hours, record a date as well as a time; a time-only entry cannot identify which day an end time belongs to.
The formula does not update after adding entries. Check whether the new rows fall inside the formula’s range. If the formula still refers to B2:B6 while the new values are in B7:B8, extend the range or use a table designed to include new records. Also check that calculation is not set to Manual under Formulas > Calculation Options; if it is, select Automatic or recalculate the workbook.
Frequently Asked Questions
What is the formula for average hours in Excel?
For time durations in B2:B10, use =AVERAGE(B2:B10), then format the result as [h]:mm. Replace the range with the cells containing your actual durations.
How do I average hours and minutes?
Enter each duration as a time value, such as 7:30, and use AVERAGE on the duration cells. Format the result as [h]:mm to display the average in hours and minutes. Excel performs the calculation on the underlying numeric time values.
How do I average more than 24 hours?
Use the same AVERAGE formula, then apply the custom number format [h]:mm. The bracketed h displays total elapsed hours rather than starting over at zero after each 24-hour period. Apply the format to any input or result that needs to show a duration above 24 hours.
Can I average decimal hours such as 7.5?
Yes. If the cells contain decimal hours, AVERAGE returns a decimal-hour result, so format it as Number. If you want the result displayed as hours and minutes, divide the average by 24 and apply [h]:mm; do not format the raw decimal-hour value as a time.
How do I average shifts that cross midnight?
Calculate each shift’s duration from its start and end times with =MOD(C2-B2,1) when the start is in B2 and the end is in C2. Fill that formula down, format the duration cells as [h]:mm, and average the resulting durations. If a shift might last longer than 24 hours, include dates as well as times.
Does AVERAGE count blank cells?
No. AVERAGE ignores blank cells and text, but it does count numeric zeros. If a blank or zero represents missing information, decide how to handle it before using the result so the average represents the records you intend to compare.
Related
- Add a Zero Before a Number in Excel Without Losing It
- How to Add a Color Key in Excel for Clearer Worksheets
- How to Add a Drop-Down List in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Group Rows in Excel by the Same NameTo group rows that share a name, first sort the entire data range by the Name column so…
- Excel Power Query: Enable Background RefreshTo enable background refresh in Excel for Microsoft 365 on Windows, open Data > Queries & Connections, right-click…
- Create a PowerPoint Template Using AI: A Practical GuideTo create a PowerPoint template using AI, ask an AI presentation tool to draft a consistent visual system…
- Excel Keyboard Shortcuts Cheat Sheet for Windows UsersFor Excel for Microsoft 365 on Windows, the most useful shortcuts include Ctrl+C to copy, Ctrl+V to paste,…
- How to Find a Table in Excel: Search and NavigationTo find a table in Excel, search for a word or value you expect it to contain, then…
- Outlook Block Sender by Domain: Setup, Limits and TipsYes. Outlook can block an entire email domain, so messages from addresses such as offers@example.com and alerts@example.com are…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.