Cellwise Start free

Excel guide · 4 min read

How to calculate MTBF and MTTR in Excel

Quick answerMTTR (mean time to repair) is total repair hours divided by the number of breakdowns. MTBF (mean time between failures) is total operating hours divided by the number of breakdowns, where operating hours are planned hours minus breakdown downtime. For one machine: =J2/I2 for MTTR and =(H2-J2)/I2 for MTBF. Availability is then MTBF / (MTBF + MTTR).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1MachineTypeDown atBack in serviceRepair hoursMachinePlanned hoursFailuresRepair hoursMTTR (h)MTBF (h)
2Press 1BreakdownOct 2, 2026Oct 2, 20263.5Press 135237.52.5114.83333333
3CNC 3BreakdownOct 5, 2026Oct 5, 20261.5Press 2352166346
4Press 2PlannedOct 7, 2026Oct 7, 20264CNC 344033.751.25145.41666667
5Press 1BreakdownOct 9, 2026Oct 10, 20262.5Lathe 435200No failuresNo failures
6CNC 3BreakdownOct 14, 2026Oct 14, 20261.5
7Press 2BreakdownOct 16, 2026Oct 16, 20266
8Press 1BreakdownOct 21, 2026Oct 21, 20261.5
9CNC 3PlannedOct 23, 2026Oct 23, 20262
10CNC 3BreakdownOct 28, 2026Oct 28, 20260.75

Download this sheet as an Excel practice file (.xlsx)

Start from a maintenance log with full date-times

Log one row per stop: machine in A, type in B (Breakdown or Planned), the moment it went down in C and the moment it was back in service in D. Type both as full date-times such as 10/9/2026 22:30, not as times alone. In the sample they're built with =DATE(2026,10,2)+TIME(8,15,0) so the example works anywhere.

Repair hours in E2 is =ROUND((D2-C2)*24,2). Excel stores a date-time as days, so the difference is a fraction of a day and x24 turns it into hours. ROUND stops tiny floating-point leftovers like 2.4999999 from making totals look off by a hair.

Decide up front what the clock measures. Most teams count from the moment the machine stopped to the moment it ran good parts again, including waiting for a technician or parts. That's "mean time to restore". If you only count hands-on wrench time, say so in the header, because the two numbers can differ by hours.

=E5

Result: 2.5

2.5: Press 1 stopped at 22:30 on 9 October and restarted at 01:00 the next day. Because both cells hold the date, the overnight stop works without any special handling.

Why times without dates break overnight repairs

If the log only holds times, a repair that crosses midnight subtracts a late time from an early one and goes negative. That one bad row quietly drags MTTR down for the whole month.

Fix the log, not the formula: export or type the date with the time. If you're stuck with times only, =MOD(D2-C2,1)*24 handles a single overnight crossing, but it can't tell a 2-hour repair from a 26-hour one, which is exactly the kind of repair you need to see.

=(TIME(1,0,0)-TIME(22,30,0))*24

Result: -21.5

-21.5: what you get for the overnight Press 1 repair if only the times 22:30 and 01:00 are stored. The real answer is 2.5 hours.

MTTR and MTBF per machine with COUNTIFS and SUMIFS

Build a summary with one row per machine (G:M in the sample) and the planned production hours for the period in H. Planned hours are the hours the machine was scheduled to run: shifts x hours x days, after breaks. Press 1 is scheduled for 352 hours in October (22 days of 16 hours); CNC 3 for 440.

Failures in I2: =COUNTIFS($A$2:$A$10,G2,$B$2:$B$10,"Breakdown"). Breakdown hours in J2: =SUMIFS($E$2:$E$10,$A$2:$A$10,G2,$B$2:$B$10,"Breakdown"). Then MTTR in K2 is =J2/I2 and MTBF in L2 is =(H2-J2)/I2: the hours it actually ran, divided by the number of times it broke.

Lathe 4 had no breakdowns, so both divisions would return #DIV/0!. Wrap them: =IF(I2=0,"No failures",(H2-J2)/I2). Don't replace it with 0: an MTBF of 0 says the machine fails constantly, the opposite of the truth.

=L2

Result: 114.83333333

114.83: Press 1 ran 344.5 of its 352 planned hours and broke down 3 times, so it ran about 115 hours between failures. Its MTTR in K2 is 2.5 hours.

Availability from MTBF and MTTR

Availability is =L2/(L2+K2): the share of planned time the machine was able to run. With MTBF calculated from uptime as above, it equals uptime / planned hours, so M2 can be the simpler =(H2-J2)/H2, which also works for Lathe 4 (100%).

The two numbers tell you what to fix. Press 2 broke down once but took 6 hours to restore (98.3% available); CNC 3 broke down three times but each repair took 1.25 hours on average (99.1%). Press 2 needs a faster repair plan, like spare parts on the shelf. CNC 3 needs fewer failures, like a look at what keeps stopping it.

=L2/(L2+K2)

Result: 0.97869318182 shows as 97.9% with Percent format

0.9787: Press 1 was available 97.9% of its planned time, the same as (352 - 7.5) / 352.

Leave planned maintenance out, and don't average the averages

Planned stops (rows with type Planned, 6 hours in total) are scheduled, so they don't count as failures and shouldn't be in planned production hours in the first place. Count them and MTBF collapses: Press 2's planned 4-hour service would turn one failure into two and cut its MTBF from 346 hours to 171. That's why both COUNTIFS and SUMIFS filter on "Breakdown".

For a fleet or a whole line, add up first and divide once: =(SUM(H2:H5)-SUM(J2:J5))/SUM(I2:I5) gives a fleet MTBF of 211.25 hours from 7 breakdowns, and =SUM(J2:J5)/SUM(I2:I5) gives a fleet MTTR of about 2.46 hours. Averaging the machine column instead gives an MTTR of 3.25, because Press 2's single 6-hour repair counts as much as CNC 3's three short ones.

=(H3-SUMIFS(E2:E10,A2:A10,"Press 2"))/COUNTIFS(A2:A10,"Press 2")

Result: 171

171: Press 2's MTBF if the planned service is wrongly counted as a failure, against 346 when only breakdowns count.

Common mistakes

Logging times without dates

Store full date-times so repairs that cross midnight (or run for days) subtract correctly.

Counting planned maintenance as failures

Add a Type column and filter COUNTIFS and SUMIFS on "Breakdown".

Using calendar hours as operating time

Use planned production hours for the period; a machine idle at weekends wasn't "running without failure" then.

Averaging per-machine MTBF or MTTR

Sum hours and failures across machines, then divide once.

FAQ

What is the formula for MTBF in Excel?

MTBF = operating hours / number of failures. With planned hours in H2, breakdown hours in J2 and failures in I2: =(H2-J2)/I2. Some teams use planned hours without subtracting downtime; pick one definition and keep it.

How do I calculate MTTR in Excel?

Divide total repair hours by the number of repairs. From a log with repair hours in E and machine names in A: =SUMIFS(E2:E10,A2:A10,"Press 1",B2:B10,"Breakdown")/COUNTIFS(A2:A10,"Press 1",B2:B10,"Breakdown").

How do I convert downtime to hours in Excel?

Subtract the start date-time from the end date-time and multiply by 24: =(D2-C2)*24. For minutes multiply by 1440. Format the result as a number, not as a time.

How is availability calculated from MTBF and MTTR?

Availability = MTBF / (MTBF + MTTR). In Excel: =L2/(L2+K2), formatted as a percentage.

Functions used

Related guides

Updated