Excel guide · 4 min read
How to calculate MTBF and MTTR in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Machine | Type | Down at | Back in service | Repair hours | Machine | Planned hours | Failures | Repair hours | MTTR (h) | MTBF (h) | |
| 2 | Press 1 | Breakdown | Oct 2, 2026 | Oct 2, 2026 | 3.5 | Press 1 | 352 | 3 | 7.5 | 2.5 | 114.83333333 | |
| 3 | CNC 3 | Breakdown | Oct 5, 2026 | Oct 5, 2026 | 1.5 | Press 2 | 352 | 1 | 6 | 6 | 346 | |
| 4 | Press 2 | Planned | Oct 7, 2026 | Oct 7, 2026 | 4 | CNC 3 | 440 | 3 | 3.75 | 1.25 | 145.41666667 | |
| 5 | Press 1 | Breakdown | Oct 9, 2026 | Oct 10, 2026 | 2.5 | Lathe 4 | 352 | 0 | 0 | No failures | No failures | |
| 6 | CNC 3 | Breakdown | Oct 14, 2026 | Oct 14, 2026 | 1.5 | |||||||
| 7 | Press 2 | Breakdown | Oct 16, 2026 | Oct 16, 2026 | 6 | |||||||
| 8 | Press 1 | Breakdown | Oct 21, 2026 | Oct 21, 2026 | 1.5 | |||||||
| 9 | CNC 3 | Planned | Oct 23, 2026 | Oct 23, 2026 | 2 | |||||||
| 10 | CNC 3 | Breakdown | Oct 28, 2026 | Oct 28, 2026 | 0.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.
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.
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.
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.
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.
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
COUNTIFSCounts with several conditionsSUMIFSAdds with several conditionsIFPicks a result from a testROUNDRounds to digitsDATEBuilds a dateTIMEBuilds a timeMODRemainder after divisionSUMAdds numbers
Related guides
- How to calculate OEE in Excel
- Calculate hours worked overnight in Excel
- Calculate hours between two dates and times in Excel
- SUMIF date range in Excel: sum between two dates
- Excel formulas cheat sheet: the 25 everyone should know
- How to make a petty cash log in Excel
- How to calculate fuel cost and mileage in Excel
Updated