How is backlog in crew-weeks calculated?

Add up the estimated hours still to do on every open order and divide by the crew hours available for backlog work each week, which is crew hours minus hours committed to meetings, training and other routine work. In the example, 224.5 remaining person-hours against 160 crew hours less 40 other hours gives 224.5 / 120 = 1.9 crew-weeks.

What counts as emergency work?

Every work type on Setup maps to one of three categories: Emergency (break-in work that drops the schedule), Corrective (repairs planned and scheduled) or Planned (preventive, predictive, improvement and compliance work). The emergency share is emergency orders divided by all orders raised in the period, cancelled ones excluded: 16 of 143 in the example, 11.2 percent against a 10 percent limit.

How does the template decide an order is overdue?

The due date is the date raised plus the target days of the order's priority, for example 14 days for P3 Normal. An open order is Overdue when the status date is after its due date and Due soon when the due date is within the window you set on Setup (3 days in the example). Completed orders show Complete, and their On time column says Yes when they finished by the due date.

Why use a typed status date instead of today's date?

A formula based on today's date changes every time the file is opened, so the same log would show different overdue counts on different days and an old copy could not be read back. The status date is the day you last brought the log up to date; move it forward when you update the rows and every age and overdue flag follows.

Which period do the dashboard figures cover?

Backlog in crew-weeks, backlog hours, open and overdue orders are a snapshot of every open order on the status date, whatever the filter. Emergency share, on-time completion, planned-work share, cost and the Pareto cover the orders raised between Raised from and Raised to; leave both blank to include every order.

Can I add more assets, technicians or priorities?

Yes. The Setup lists and the work order table are Excel tables that grow as you type. The dashboard's by-priority, by-technician and cost Pareto blocks are sized to the example lists; the Method sheet says how to extend them and the dashboard shows a warning when a Setup list is longer than its block.

Maintenance and Reliability

Maintenance Work Order Log Excel Template

Keep every maintenance job in one table and see, as of the date you update it, how many weeks of work are waiting for the crew, what is overdue, how much is emergency break-in work and which assets take the money.

Overview

This maintenance work order log is a working Excel tool for maintenance managers, planners and supervisors who run the work from a spreadsheet or a thin CMMS. It ships with eight weeks of example work orders for a fictional food plant with twelve assets and a four-technician crew, including a bottle filler that breaks down more than anything else and a few jobs held up waiting for parts, so the dashboard has a story on day one.

Each row is one job. The workbook takes the due date from the priority's target days on Setup, marks the order open or closed from its status, measures its age to the status date you type, and keeps the estimated hours still to do as the order's share of the backlog. Labour cost is actual hours times your loaded rate, and total cost adds parts and contractors. Open orders read Overdue, Due soon or OK in words, completed orders say whether they beat their due date, and a Check column catches the data errors a real log collects.

The dashboard separates the snapshot from the period. Backlog in crew-weeks is the remaining hours on every open order divided by the crew hours left each week after meetings, training and other commitments, the same formula as the Maintenance Backlog Weeks calculator on this site. Emergency work share, on-time completion, planned-work share and cost cover the orders raised between the dates you choose, with a weekly raised, completed and open trend and a cost Pareto that points at the bad actors.

What this template includes

Work order table with dropdowns for asset, work type, priority, technician and status, and data validation on every input
Due date from each priority's target days, open or closed, age in days, remaining backlog hours, labour and total cost per order
Due status in words (Overdue, Due soon, OK, Complete) against a status date you set, and whether each completed order finished on time
A Check column that names data errors: status and completion date out of step, completed before raised, dates after the status date, duplicate numbers and items missing from the Setup lists
Setup for the labour rate, crew hours per week, hours committed elsewhere, your own targets, and the asset, work type, priority and technician lists
Dashboard with backlog in crew-weeks, emergency work share, on-time completion and overdue orders against your targets, filtered by date raised
Charts of work orders raised, completed and still open by week, and a Pareto of maintenance cost by asset
Roll-ups by work category, by priority and by technician, a Method sheet with every formula, and a Verify sheet that re-checks them against an independent reference and two live calculators

Suggested use case

Use it for the weekly maintenance planning meeting, to show management the backlog and the cost of breakdowns, to find the bad-actor assets worth an improvement project, or as a stepping stone before buying a CMMS.

How to use it

On Setup, set the status date, the loaded labour rate, the crew's weekly hours and the hours committed elsewhere, and your backlog, emergency and on-time targets.
Replace the example assets, work types (each mapped to Emergency, Corrective or Planned), priorities with their target days, and technicians.
On Work Orders, add one row per job and keep it current: status, estimated and actual hours, parts and contractor cost, and the completion date when it is done.
Fix anything the Check column reports, then move the status date forward to the day you updated the log.
Open the Dashboard and set Raised from and Raised to for the month or quarter you are reviewing.

Frequently Asked Questions

How is backlog in crew-weeks calculated?: Add up the estimated hours still to do on every open order and divide by the crew hours available for backlog work each week, which is crew hours minus hours committed to meetings, training and other routine work. In the example, 224.5 remaining person-hours against 160 crew hours less 40 other hours gives 224.5 / 120 = 1.9 crew-weeks.
What counts as emergency work?: Every work type on Setup maps to one of three categories: Emergency (break-in work that drops the schedule), Corrective (repairs planned and scheduled) or Planned (preventive, predictive, improvement and compliance work). The emergency share is emergency orders divided by all orders raised in the period, cancelled ones excluded: 16 of 143 in the example, 11.2 percent against a 10 percent limit.
How does the template decide an order is overdue?: The due date is the date raised plus the target days of the order's priority, for example 14 days for P3 Normal. An open order is Overdue when the status date is after its due date and Due soon when the due date is within the window you set on Setup (3 days in the example). Completed orders show Complete, and their On time column says Yes when they finished by the due date.
Why use a typed status date instead of today's date?: A formula based on today's date changes every time the file is opened, so the same log would show different overdue counts on different days and an old copy could not be read back. The status date is the day you last brought the log up to date; move it forward when you update the rows and every age and overdue flag follows.
Which period do the dashboard figures cover?: Backlog in crew-weeks, backlog hours, open and overdue orders are a snapshot of every open order on the status date, whatever the filter. Emergency share, on-time completion, planned-work share, cost and the Pareto cover the orders raised between Raised from and Raised to; leave both blank to include every order.
Can I add more assets, technicians or priorities?: Yes. The Setup lists and the work order table are Excel tables that grow as you type. The dashboard's by-priority, by-technician and cost Pareto blocks are sized to the example lists; the Method sheet says how to extend them and the dashboard shows a warning when a Setup list is longer than its block.