How to Calculate Average Time and Durations in Excel

To get an average time in Excel, use =AVERAGE() on cells that hold real time values and format the answer as h:mm. That works for call lengths, start times and shift durations. Three cases need extra care: averages over 24 hours, clock times that cross midnight, and times stored as text. This guide covers all of them for anyone tracking time in a report or a job test.

How Excel stores time#

Excel stores a time as a fraction of a day. Midnight is 0, noon is 0.5 and 6:00 AM is 0.25. One hour is 1/24 and one minute is 1/1440.

Because times are numbers, AVERAGE, SUM, MIN and MAX all work on them. The format decides whether you see 0.5 or 12:00. Dates work the same way, as whole numbers; see why Excel dates are numbers.

How to average time in Excel#

Average times with AVERAGE, then apply a time format to the result.

1. Click an empty cell, such as D2.
2. Type =AVERAGE(B2:B6) and press Enter.
3. If you see a decimal like 0.058333, press Ctrl+1 (Mac: ⌘1), choose Custom and type h:mm.

The result is 1:24. The five calls add up to 7 hours (420 minutes), and 420 ÷ 5 = 84 minutes. Use h:mm:ss if you need seconds. The AVERAGE function guide covers what AVERAGE includes and skips.

The same works for clock times. If four people started work at 8:55, 9:10, 9:05 and 8:42, =AVERAGE(B2:B5) returns 8:58, the average start time.

Average durations over 24 hours: use [h]:mm#

The value AVERAGE returns is correct even when it's more than a day. The problem is the display: h:mm shows only the hours left over after whole days.

Say three projects took 30:00, 26:30 and 21:00 hours. The average is 25 hours 50 minutes, but with h:mm the cell shows 1:50, because 24 of the hours become a hidden day.

Fix it with a custom format that has square brackets:

[h]:mm

Now the cell shows 25:50. The brackets tell Excel to keep counting hours past 24. Use [m]:ss for total minutes and seconds. The guide to custom number formats explains the codes, and the guide to time difference in Excel uses the same format for totals.

Show the average in minutes or decimal hours#

Multiply a time by the number of units in a day to turn it into a plain number:

Format these results as General or Number, not as time. A decimal-hours number is handy for pay or billing, because you can multiply it by an hourly rate.

The opposite is useful too. If your data is in plain minutes (85, 92, 78…), average them and divide by 1440 to get a time:

=TEXT(AVERAGE(B2:B6)/1440,"h:mm")

This returns the text 1:24. TEXT is great for labels and sentences, such as ="Average call: "&TEXT(AVERAGE(B2:B6),"h:mm"). But the result is text, so you can't do more maths with it. The TEXT function guide lists the time codes.

Average clock times that cross midnight#

Plain AVERAGE fails for times on both sides of midnight. Take four bedtimes: 23:30, 0:30, 23:00 and 1:00. To Excel, 0:30 and 1:00 are early in the day, so the average comes out as 12:00: noon, which is clearly wrong.

The fix is to add 1 (one day) to the after-midnight times before averaging, then remove whole days from the result:

=MOD(AVERAGE(IF(B2:B5<0.5,B2:B5+1,B2:B5)),1)

• B2:B5<0.5 finds times before noon, which here means after midnight.
• +1 moves those times into the next day, so 0:30 counts as 24:30.
• MOD(…,1) keeps only the time part of the answer.

Formatted as h:mm, the result is 0:00: midnight, which matches the data. In Microsoft 365 and Excel 2021 this works as a normal formula. In Excel 2019 and older, confirm it with Ctrl+Shift+Enter. Adjust the 0.5 cut-off if your data has real morning times. Shift lengths have a similar problem; adding hours to a time shows how MOD handles times that roll past midnight.

Fix text times, blanks and zeros#

AVERAGE only uses numbers, so a few kinds of cells change the answer without warning:

• Times stored as text. Imported values like "1:25" may be text. They usually sit on the left of the cell, and AVERAGE skips them; if every cell is text, you get #DIV/0!. Convert a column with =VALUE(B2) in a helper column, then average that.
• Blank cells. AVERAGE ignores truly empty cells, which is usually what you want.
• Zero times. A 0:00 entry counts as a real value and drags the average down. To leave zeros out, use =AVERAGEIF(B2:B6,">0").
• Averages by group. For one team's average, use =AVERAGEIF(A2:A20,"Team A",B2:B20) and format it as h:mm.

The guide to AVERAGE, MIN and MAX shows how to find the shortest and longest time in the same list.

Average the time between two columns#

If you have a start time and an end time on each row, find each duration first, then average:

1. In D2, type =C2-B2 and fill down. Use =MOD(C2-B2,1) if any shift ends after midnight.
2. Average the durations: =AVERAGE(D2:D6).
3. Format the result as h:mm, or [h]:mm for long durations.

In Microsoft 365 you can skip the helper column with =AVERAGE(C2:C6-B2:B6), but the helper column makes your work easier to check.

Practise it#

Averages and time values both come down to numbers and formats. Practise AVERAGE, MIN and MAX in a live spreadsheet to summarise a list in seconds, then learn how Excel stores dates as numbers, which is the same idea that makes time maths work. Try the exercise on this page to turn an average in minutes into h:mm.