How to Calculate Average Hours in Excel

Averaging hours in Excel looks like a one-word job, AVERAGE, until days off drag the result down, time formats display nonsense, or night-shift start times average out to the middle of the afternoon. This guide works through each case on one small timesheet. For a one-off answer without a spreadsheet, the hours calculator totals shifts for you.

The example timesheet

Rows 2–8 hold one week: A is the date, B the time in, C the time out. Column D calculates each day’s hours as a time value and column E converts them to decimal hours:

=IF(C2="","",MOD(C2-B2,1))
=IF(D2="","",ROUND(D2*24,2))

The IF wrapper returns an empty string on days off instead of 0, which matters for averages. Format D as h:mm and E as 0.00 (Format Cells, Ctrl+1 or Cmd+1 → Custom). If you are building the hours column from scratch, start with calculate hours worked in Excel.

The week totals 39.00 hours over five worked days.

Average daily hours

For decimal hours:

=AVERAGE(E2:E8)

This returns 7.80. For the time column:

=AVERAGE(D2:D8)

This returns 7:48 when formatted h:mm. Both are the same amount: 0.80 hours is 48 minutes (0.80 × 60), so do not read 7.80 as 7 hours 80 minutes, or as 7:08.

Blank cells, zeros and empty strings

AVERAGE skips truly blank cells and text, including the empty string ("") returned by the IF formulas above. It does not skip zeros. If column D used plain =MOD(C2-B2,1), Saturday and Sunday would show 0:00 and the same AVERAGE would return 39 ÷ 7 = 5.57 hours. To leave out zero days no matter how they got there:

=AVERAGEIF(E2:E8,">0")

This also returns 7.80. Decide which number you actually want: the average per day worked (7.80) or per calendar day (5.57). Avoid AVERAGEA here, because it counts text, including "", as zero. Google Sheets uses the same AVERAGE, AVERAGEIF and AVERAGEIFS functions; for time results, choose Format → Number → Duration.

Average weekly hours from a daily log

For a log covering several weeks, do not average the daily rows and multiply by five; that assumes everyone works a five-day week. Total each workweek first, then average the totals. With four weeks of dates in A2:A29 and decimal hours in E2:E29, add a week key in F2 and fill down:

=A2-WEEKDAY(A2,2)+1

This returns the Monday that starts each row’s week (format it as a date). =ISOWEEKNUM(A2) also works within a single year. List the four week-start dates in H2:H5 and total each week in I2, filled down to I5:

=SUMIFS($E$2:$E$29,$F$2:$F$29,H2)

Then average the weekly totals:

=AVERAGE(I2:I5)

The result is 39.50 hours per week. As a time value, =AVERAGE(I2:I5)/24 formatted [h]:mm shows 39:30. The brackets matter: plain h:mm would wrap past 24 and display 15:30. In Google Sheets, use Format → Number → Duration, or a custom number format of [h]:mm.

To average hours per day worked within a given week, add a criterion that skips days off:

=AVERAGEIFS($E$2:$E$29,$F$2:$F$29,H2,$E$2:$E$29,">0")

If the log does not break into whole weeks, divide total hours by the number of weeks it spans:

=SUM(E2:E29)/((MAX(A2:A29)-MIN(A2:A29)+1)/7)

For the 28 days from Sep 14 to Oct 11 this gives the same 39.50.

Average hours per employee

When several people share one log, let AVERAGEIFS do the grouping instead of building a separate sheet for each person. On a team log with dates in A, names in B and decimal hours in C:

Type each name once in E2:E3 (in Excel 365, =UNIQUE(B2:B7) lists them for you). Then count days worked in F2 and average them in G2, filled down:

=COUNTIFS($B$2:$B$7,E2,$C$2:$C$7,">0")
=AVERAGEIFS($C$2:$C$7,$B$2:$B$7,E2,$C$2:$C$7,">0")

Ana worked 3 days averaging 8.00 hours; Ben worked 2 days averaging 7.00. Without the ">0" condition, Ben’s zero-hour day would pull his average down to 4.67. A PivotTable gives the same answer without formulas: put Employee in Rows and Hours in Values, then change the summary from Sum to Average under Value Field Settings.

Average start times across midnight

Averaging clock times works for day shifts: =AVERAGE(B2:B6) on the example sheet returns 8:12 AM. It breaks for night shifts. Excel stores 11:30 PM as 0.979 and 12:10 AM as 0.007, so a plain average of times on both sides of midnight lands near noon. Consider these clock-in times on a separate sheet:

=AVERAGE(B2:B6) returns 2:00 PM, which is obviously wrong. Add a helper column that pushes early-morning times into the next day, then wrap the average back onto a 24-hour clock:

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

Formatted h:mm AM/PM, the result is 11:36 PM. The 0.5 cutoff (noon) assumes nobody clocks in during the late morning; move it if your shifts differ. A single formula does the same without the helper column:

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

Excel 365 and 2021 calculate it directly; in older versions confirm it with Ctrl+Shift+Enter. In Google Sheets, wrap the AVERAGE part in ARRAYFORMULA. For shift lengths that cross midnight, see Excel night shift hours.

Weighted averages

An average of averages misleads when the groups differ in size. Suppose three teams have their shift counts in B2:B4 and average shift lengths in C2:C4:

=AVERAGE(C2:C4) returns 8.58, even though most shifts are short swing shifts. Weight each average by its count:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)

This returns 7.99: 399.5 total hours ÷ 50 shifts. The same pattern gives an hours-weighted average pay rate: SUMPRODUCT of hours and rates, divided by total hours.

Median and rounding

When a few unusually long or short days skew the average, the median is a better picture of a typical day:

=MEDIAN(E2:E8)

This returns 8.50 for the example week. Like AVERAGE, MEDIAN ignores blanks and text but counts zeros; with 0 on Saturday and Sunday it would return 6.75. In Excel 365 you can filter zeros out first:

=MEDIAN(FILTER(E2:E8,E2:E8>0))

Formatting changes what you see, not what Excel stores: 0.00 shows 7.80 but keeps every digit for later math. To store a rounded value, use the first formula below for decimal hours and the second to round a time-value average to the nearest minute:

=ROUND(AVERAGE(E2:E8),2)
=ROUND(AVERAGE(D2:D8)*1440,0)/1440

Round once, at the end. Rounding each day first can shift the average by a minute or two. The decimal to time calculator is a quick way to check a result such as 7.80 = 7:48.

Common errors and fixes

• The average shows 0.325 or 1/0/1900. The value is right but the format is wrong: 0.325 of a day is 7.8 hours. Format a time average as h:mm or [h]:mm, or multiply by 24 for decimal hours, as covered in convert time to decimal in Excel.
• The average is too low. Zeros on days off are being counted. Use AVERAGEIF with ">0", or return "" for days off.
• Days seem to be missing. AVERAGE silently skips text, so times pasted as text drop out. Compare =COUNT(D2:D8) with the number of days worked, and convert text with =TIMEVALUE(B2) or =--B2.
• A weekly average shows 15:30 instead of 39:30. Use [h]:mm for any result that can exceed 24 hours.
• The average clock-in time is in the afternoon. The times straddle midnight; use the MOD adjustment above.
• #DIV/0! No cells meet the criteria yet, such as a new week with no hours entered. Wrap the formula in IFERROR so the cell stays blank until there is data:

=IFERROR(AVERAGEIF(E2:E8,">0"),"")