Calculate Time-Weighted Average in Excel with Examples

How-to basics · Updated November 21, 2026

A time-weighted average in Excel is the sum of each value multiplied by the time it applies, divided by the total time covered. For interval-based data, enter each interval’s start, end, and value, then use =SUMPRODUCT(C2:C5,B2:B5-A2:A5)/SUM(B2:B5-A2:A5) to calculate the average when starts are in A, ends are in B, and values are in C.

What a time-weighted average measures

A regular average gives every reading equal influence, regardless of how long it lasted. A time-weighted average gives a reading more influence when it applies for a longer period. It is useful when measurements are taken at irregular times or when a value remains in effect between observations.

For example, if a room is at 10 degrees for one hour and 20 degrees for three hours, the time-weighted average is 17.5 degrees, not the simple average of 15. The calculation is (10 × 1 + 20 × 3) ÷ 4. The same principle applies to rates, concentrations, staffing levels, and other measurements, provided the values and time periods are interpreted consistently.

The essential choice is what each observation represents. A reading might apply from its timestamp until the next reading, or it might describe a separately recorded interval with a clear start and end. Those assumptions produce different formulas, so decide how the readings relate to time before calculating.

Set up an interval-based table

For the most straightforward calculation, use one row per interval. In a new worksheet, create these columns:

  • Column A: interval start date and time.
  • Column B: interval end date and time.
  • Column C: the value that applied throughout that interval.

For a worked example, enter these rows: 8:00 to 9:00 with a value of 10; 9:00 to 11:00 with a value of 20; 11:00 to 12:00 with a value of 30; and 12:00 to 14:00 with a value of 25. Put the entries in rows 2 through 5, with starts in A, ends in B, and readings in C. Use actual Excel date-time values, not text that merely looks like a time.

In this example, the intervals last one, two, one, and two hours. The weighted sum is 10 × 1 + 20 × 2 + 30 × 1 + 25 × 2, or 135 value-hours. The total duration is six hours, so the result is 135 ÷ 6 = 22.5. The output is in the same units as the values in column C.

Calculate the average with SUMPRODUCT

With interval starts in A2:A5, ends in B2:B5, and values in C2:C5, enter this formula in an empty cell:

=SUMPRODUCT(C2:C5,B2:B5-A2:A5)/SUM(B2:B5-A2:A5)

Excel stores dates and times as serial numbers, with one day represented by 1. Subtracting a start from an end therefore gives the interval length in days. The same time unit appears in both the numerator and denominator, so it cancels out; you do not need to multiply the durations by 24 to get an average in hours. For the example above, this formula returns 22.5.

To make the calculation easier to audit, you can also add a duration column. With starts in A2:A5 and ends in B2:B5, enter =B2-A2 in D2 and fill it down through D5. Format the duration cells with a suitable time format. Then, with values in C2:C5 and durations in D2:D5, calculate the result using =SUMPRODUCT(C2:C5,D2:D5)/SUM(D2:D5). The helper column makes it easier to spot an interval that is too long, too short, or negative.

Use timestamps when each reading lasts until the next

Sometimes the worksheet contains a timestamp and reading in each row, rather than a separate start and end for every interval. If each reading applies from its timestamp until the next timestamp, sort the records from earliest to latest. For timestamps in A2:A5 and readings in B2:B5, use:

=SUMPRODUCT(B2:B4,A3:A5-A2:A4)/(A5-A2)

This formula pairs the readings in rows 2 through 4 with the elapsed times until the next timestamp. The reading in row 5 does not contribute because there is no later timestamp to establish how long it applies. The denominator is the full time from the first timestamp in A2 to the last timestamp in A5. This method assumes the value stays constant between observations; it does not assume the value gradually changes.

For instance, timestamps at 8:00, 9:00, 11:00, and 14:00 with readings of 10, 20, 30, and 25 assign the first three readings durations of one, two, and three hours. The average is (10 × 1 + 20 × 2 + 30 × 3) ÷ 6, or 23.33. The last reading marks the end of the calculation window but has no duration in this setup. If you know that the final reading should apply after 14:00, add an explicit ending time and handle it as another interval.

Choose between stepwise and interpolated values

The timestamp formula above uses a stepwise assumption: each reading holds steady until the next one. This is often appropriate for settings that change at recorded times, such as a posted rate or a machine setting. For a quantity that changes continuously between readings, you may instead want to assume a straight-line change and use the average of each interval’s two endpoint readings.

With timestamps in A2:A5 and readings in B2:B5, where each interval connects consecutive rows, calculate a straight-line, or trapezoidal, time-weighted average with:

=SUMPRODUCT((B2:B4+B3:B5)/2,A3:A5-A2:A4)/(A5-A2)

Each pair of readings is averaged, multiplied by the time between its timestamps, and then divided by the complete elapsed time. For the four readings 10, 20, 30, and 25 at 8:00, 9:00, 11:00, and 14:00, the interval averages are 15, 25, and 27.5. Their weighted sum is 15 × 1 + 25 × 2 + 27.5 × 3 = 147.5; divided by six hours, the result is about 24.58. This differs from the stepwise result because the assumed path between readings differs. Choose the model that matches how the measurement behaves, rather than choosing a formula only because it produces a preferred result.

Format dates, times, and durations correctly

Enter full date-and-time values when the data may span multiple dates. A time-only value such as 23:00 followed by 01:00 can appear to run backward if Excel interprets both times as belonging to the same day. Using dates as well as times makes the elapsed interval unambiguous. For overnight intervals, for example, record the start as 11:00 PM on one date and the end as 1:00 AM on the next date.

Excel’s display format does not change the underlying calculation, but it can make durations easier to inspect. Select the duration cells, press Ctrl+1 on Windows, choose the Number tab, select Custom, and enter [h]:mm to display elapsed hours and minutes, including durations longer than 24 hours. On Mac, use Command+1 to open Format Cells. If Excel shows a decimal such as 0.5, that represents half a day; it does not mean the formula is wrong. Format the result cell as a number if you want to see the average measurement rather than a time display.

Check the data and troubleshoot common errors

Before relying on the result, check that every interval has a positive duration and that the intervals cover the intended period. A negative duration usually means a start and end were reversed, a date was entered incorrectly, or time-only values crossed midnight. A zero duration contributes no time and can signal duplicate timestamps or an interval that needs clarification.

  • Confirm the cells are numeric dates and times. If Excel treats an entry as text, subtraction may return an error or an unexpected result. Re-enter it in a recognized date-time format or convert the source data before calculating.
  • Sort timestamp records chronologically. The timestamp-only formulas use adjacent rows to determine elapsed time. An out-of-order row can create incorrect or negative intervals.
  • Check gaps and overlaps. Gaps may mean the measurement is unknown for part of the period; overlapping intervals may count some time more than once. Decide how to handle them rather than silently treating the result as complete.
  • Use matching ranges. The values and durations supplied to SUMPRODUCT must have the same number of rows. In timestamp layouts, the reading and duration ranges intentionally stop one row before the final timestamp.
  • Handle missing readings deliberately. Do not assume a blank means zero unless zero is truly the measured value. A blank may mean unknown, not recorded, or not applicable; each interpretation changes which time should be included.

A useful check is to compare the result with the smallest and largest readings included in the calculation. If all durations are positive and all readings are numeric, a conventional weighted average should fall between those values. Also calculate the total interval duration separately and compare it with the expected reporting window. These checks will not prove every assumption is right, but they can reveal common data and range mistakes.

Compare with a regular average and other options

Excel’s AVERAGE function treats each numeric entry equally. It is appropriate when observations represent equally sized time periods or when the question is simply the average of the listed readings. It is not a substitute for time weighting when one reading represents ten minutes and another represents several hours. In the worked interval example, a simple average of 10, 20, 30, and 25 is 21.25, while the time-weighted result is 22.5 because the values apply for unequal durations.

For a small dataset, the direct SUMPRODUCT formula is usually compact. A helper duration column is more transparent when other people need to review the worksheet or when interval lengths need checking. For recurring reports, a structured Excel Table can make the data easier to maintain, but the key requirement remains the same: calculate valid durations and weight each value by the time it represents. Avoid copying a formula down without checking whether its ranges and endpoint assumptions still match the data.

Frequently Asked Questions

Can I use SUMPRODUCT for a time-weighted average in Excel?
Yes. Multiply the values by their corresponding durations with SUMPRODUCT, then divide by the sum of those durations. Ensure that the formula uses matching value and duration ranges and that the duration cells contain numeric elapsed times.

Does Excel need the durations to be in hours?
No. Excel can store elapsed time as fractions of a day, and the common time scale cancels between the weighted sum and total duration. You can use days, hours, or minutes, as long as all durations use the same units.

Should I include the final timestamp reading?
Include it only if you know how long it applies. In a timestamp-only setup where each reading lasts until the next one, the last timestamp defines the end of the covered window, so its reading has no interval after it. Add a known end time if the final value should continue beyond that timestamp.

Why is my result different from AVERAGE?
AVERAGE gives every reading equal weight. A time-weighted calculation gives longer intervals more influence. The two results match when the observations cover equal durations or happen to produce the same numerical outcome.

How do I handle an interval that crosses midnight?
Use full date-and-time values for both its start and end, with the end date set to the following day. A time-only end earlier than its start can otherwise look like a negative duration.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.