How to Calculate the Average of the Time Values in Excel (Formula)

Last Updated: November 21, 2023
puneet-gogia-excel-champs

- Written by Puneet

In this tutorial, we will learn to calculate the average of the time values that you have in a range of cells.

Formula to Average Time

In the following example, you have three-time values in column A in the range A1:A3. Now you need to calculate the average of these time values.

calculate-average-of-time-values

For this, you need to use the AVERAGE function.

  1. Enter the average function in a cell by using the equal sign.
  2. Then enter the AVERAGE function and start parentheses.
  3. Refer to the range where you have the time.
  4. In the end, enter the closing parentheses and hit enter to get the result.
average-to-find-time-value-average
=AVERAGE(A1:A3)

In the cell where you want to calculate the average of the time, you need to make sure to have the time format on that cell. Even if you don’t change the format, Excel is smart enough to change this format for you when you enter the formula in it.

Understand Time in Excel

In Excel, when you enter time in a cell Excel gives it a format automatically. There are two ways to format a time value one is in a 12-hour format and the second is in a 24-hour format. When you are averaging time, and you have a 12-hour format, you need to be a little cautious.

understand-time-in-excel

Above we have two different times one is 6:30 AM and the second is 6:30 PM. That’s why the average is different with both formulas.

Why I’m Getting #DIV/0! Error While Averaging Time

If your time values are stored as a text value in the cell, you might face #DIV/0! error. Below we have a list of time values that are saved as text and on averaging we are getting an error.

#div-error-while-averaging-time

To solve this problem, you can use the following formula.

formula-to-ignore-#div-error

You need to enter this formula as an array formula by using Ctrl + Shift + Enter.

Get the Excel File