Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Calculate Average Time in Excel Pivot Table

Cody Schneider••6 min read

Calculating the average time in an Excel pivot table seems like it should be straightforward, but it often trips people up. The reason is simple: Excel handles time as a fraction of a 24-hour day, which can lead to confusing results unless you know a specific trick. This tutorial will walk you through the correct way to prepare your data, create your pivot table, and accurately calculate average time.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why is Calculating Average Time in Excel So Tricky?

The main challenge comes from how Excel stores time values. To Excel, a day is represented by the number 1. A time value is just a decimal fraction of that number. For example:

  • 6:00 AM is stored as 0.25 (a quarter of a day)
  • 12:00 PM (Noon) is stored as 0.5 (half a day)
  • 6:00 PM is stored as 0.75 (three-quarters of a day)

When you ask a pivot table to calculate the "Average" of a time field, it correctly averages these decimal values. However, it often displays the result in a standard time format (like hh:mm AM/PM), which interprets the resulting decimal as a specific point in time, not a duration. This usually results in a nonsensical value like '12:05 AM', leaving you confused.

To get a meaningful result, you need to tell Excel to format the calculated average as a duration, not a time of day.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 1: Prepare and Format Your Source Data

Clean source data is the foundation of any accurate pivot table. Before you start building, make sure your data is set up correctly.

First, ensure your time and duration data is in an Excel table or a well-structured range with clear headers. Each row should be a distinct record, and each column should be a specific field.

For this example, let's use a dataset that tracks project tasks, including start and end times.

Your table might look something like this:

Calculate the Duration

Next, you'll need a column for the duration of each task. This is a simple subtraction formula.

  1. Add a new column header called "Duration".
  2. In the first cell of that column, enter the formula to subtract the start time from the end time. For instance, if your data starts in row 2 and "End Time" is in column D and "Start Time" is in column C, the formula would be: =D2-C2
  3. Drag this formula down for all your rows.

At this point, you'll have a column filled with decimal numbers or times. You need to format this "Duration" column properly.

Apply the Correct Time Format

Select your entire "Duration" column.

  1. Right-click and choose Format Cells.
  2. Go to the Number tab and select Custom from the category list.
  3. In the "Type" input box, enter this format code: [h]:mm:ss

What this format means:

  • The brackets around the "h" - [h] - are critical. They tell Excel to display the cumulative number of hours, even if it exceeds 24. A standard h would restart at zero every 24 hours.
  • mm represents minutes.
  • ss represents seconds.
GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 2: Create Your Pivot Table

Now that your data is properly formatted, you can create the pivot table.

  1. Click anywhere inside your data table.
  2. Go to the Insert tab on the ribbon and click PivotTable.
  3. Excel will automatically select your data range. Confirm it's correct and choose whether to place the pivot table in a new worksheet or an existing one. A new worksheet is usually best.
  4. Click OK.

You now have a blank pivot table canvas and a "PivotTable Fields" pane on the right. Let's say we want to find the average task duration for each person.

  1. From the "PivotTable Fields" pane, drag "Assigned To" into the Rows area.
  2. Drag the "Duration" field into the Values area.

Step 3: Correctly Calculate the Average Time

By default, the pivot table will likely display "Sum of Duration" or "Count of Duration." You need to change this to average the values.

  1. In the "Values" area of the "PivotTable Fields" pane, click on the field you just added (e.g., "Sum of Duration").
  2. Select Value Field Settings... from the menu.
  3. In the "Summarize value field by" list, choose Average.
  4. Click OK.

Applying the Final Formatting Fix

This is where most people get stuck. After setting the summarization to "Average," your pivot table will likely show a strange decimal number or a time like 12:47 AM. This is the raw average of Excel's time decimals, displayed with default formatting.

Here's how to fix it once and for all:

  1. Right-click on any of the values in the "Average of Duration" column within your pivot table.
  2. Select Format Cells... (do NOT use "Number Format" from the Value Field Settings, as it sometimes behaves differently).
  3. Go to the Number tab and select Custom.
  4. Just like before, enter the format code in the "Type" box: [h]:mm:ss
  5. Click OK.

Your pivot table will instantly update to show a readable, accurate average duration in hours, minutes, and seconds. Problem solved!

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Troubleshooting Common Issues

If you're still having trouble, here are a few things to check.

  • Times Imported as Text: Sometimes, when you import data, times can be formatted as text instead of a number. You can often fix this by using a helper column with the =TIMEVALUE() function to convert the text to a true Excel time value.
  • Inconsistent Date/Time Entries: If your start and end times also include dates (e.g., 1/15/2024 9:00 AM), your duration calculation (End - Start) will also include the days. The [h]:mm:ss format will correctly display total hours even if it spans multiple days.
  • Blank Cells in Data: Be aware that blank cells in your "Duration" column will be ignored by the "Average" calculation. This is usually the desired behavior, but if you want to treat them as zero, use Find & Replace to change blanks to 0 in your source data.

Final Thoughts

Finding the average of time values in an Excel pivot table boils down to one key step: applying the correct custom number format. By using [h]:mm:ss after setting your value field to average your duration data, you instruct Excel to display the result as a cumulative duration rather than a specific time of day.

Spending half a day wrestling with spreadsheet peculiarities is a common frustration, especially when you need to answer a simple business question quickly. It's the kind of manual work that holds teams back. To avoid this, we built Graphed which connects directly to your data sources and automates these types of analyses. Instead of formatting cells and building pivot tables, you can simply ask, "What was the average project task duration per team member last quarter?" and instantly get a live, shareable dashboard.

Related Articles