How to Calculate Average Time in Google Sheets


You can use the following formula to calculate an average time value in Google Sheets:

=AVERAGE(A2:A11)

This particular formula calculates the average time value in the range A2:A11 and assumes that each value in the range is in a valid time format.

The following example shows how to use this formula in practice.

Example: Calculate Average Time in Google Sheets

Suppose we have the following list of times in Google Sheets:

To verify that the times are in a valid format, we can highlight the range A2:A11, then click the Format tab along the top menu, then click Number, then check to see the number format being used:

We can see that Google Sheets has correctly identified the values in the range A2:A11 as Time.

Next, we can use the following formula to calculate the average time value:

=AVERAGE(A2:A11)

The following screenshot shows how to use this formula in practice:

Google Sheets calculate average time

We can see that the average time is 12:11:51 PM.

Example: Calculate Average Time with Condition in Google Sheets

We can also use an AVERAGEIF function to calculate the average time based on a condition in Google Sheets.

For example, we can use the following formula to calculate the average time only for the times that occur after 12 PM:

=AVERAGEIF(A2:A11, ">12:00:00 PM")

The following screenshot shows how to use this formula in practice:

Google Sheets returns a numerical value but we can click the Format tab again, then click the Number, then click Time to convert this numeric value to a time value:

The average time among the times that occur after 12 PM is 5:18:19 PM.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Extract Hour from Datetime
Google Sheets: How to Convert Time Duration to Minutes
Google Sheets: How to Combine Date & Time

Leave a Reply

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