How to average time and durations in Excel

Average a column of durations

Column D holds how long each driver took to load the van. These are real time values: 0:25 is stored as 0.01736, which is 25 minutes as a fraction of a day. AVERAGE adds them and divides by the count, exactly as it would with any numbers.

The catch is the display. The result is 0.02697, and depending on the cell's format Excel may show that decimal instead of a time. Press Ctrl+1 and pick a time format, or use Custom with h:mm:ss. The formula here wraps the average in TEXT so you can see it as a time.

Show the average in minutes (or hours)

Many reports want "38.8 minutes" rather than 0:38:50. Multiply the average by 1440, the number of minutes in a day, and round it. For hours, multiply by 24 instead. Format this result as Number or General, not as a time, or Excel will show it as a strange clock time.

If an average duration can be 24 hours or more, for example ticket resolution times, the h:mm format resets at every 24 hours and 30 hours shows as 6:00. Use the custom format [h]:mm, or convert to hours with *24, and the total stays honest.

Average shift length when shifts cross midnight

To average how long each shift lasted, you first need each shift's duration. Clock out minus clock in works for Jo (16:45 to 23:15), but Priya clocks out at 2:30, which Excel sees as earlier than 18:00. The subtraction is negative, and averaging negative times gives nonsense or a row of ##### signs.

MOD(end-start,1) adds a day to any negative difference, so 2:30 minus 18:00 becomes 8:30. In Excel 365 and 2021 you can do it all in one cell, as below. In older versions, put =MOD(C2-B2,1) in a helper column, fill it down and average that column. That helper column is worth having anyway, because you can check each shift by eye.

Text times give #DIV/0!

Column E has the same load times, but they came from a CSV export and landed as text. They look identical. The clue is that they sit on the left of the cell, and =ISTEXT(E2) returns TRUE. AVERAGE skips text, so it finds no numbers at all and divides by zero.

If only some of the cells are text, it is worse: there is no error, AVERAGE just quietly ignores those rows and the answer is wrong. Count with =COUNT(E2:E7) and compare it with the number of rows; any gap means text.

Fix text times: TIMEVALUE or Text to Columns

TIMEVALUE turns text such as "0:25" into a real time. In Excel 365 and 2021, give it the whole range inside AVERAGE. In older Excel, convert in a helper column with =TIMEVALUE(E2), fill down, and average that.

For a permanent fix, convert the column in place so every later formula works too. Text to Columns does it without any formula.

1. Select E2:E7.
2. Go to Data > Text to Columns.
3. Choose Delimited, click Next, clear every delimiter box and click Next.
4. Leave the column format as General and click Finish. Then apply a time format with Ctrl+1.

Average clock-out time around midnight

Averaging clock times (not durations) has its own trap. Most drivers finish between 1:20 and 7:10 in the morning, but Jo finishes at 23:15. To Excel, 23:15 is late in the day and 2:30 is early, so a plain AVERAGE(C2:C7) gives 7:20 AM, later than every driver's actual finish.

The fix is to treat the morning times as belonging to the next day: add 1 to any time before noon, average, then use MOD to bring the result back to a clock time. This works when all the times sit within 12 hours either side of midnight. For daytime start times like column B, a plain average is fine: =TEXT(AVERAGE(B2:B7),"h:mm AM/PM") gives 7:10 PM.