Build a Maintenance Schedule in Excel

Machines, vehicles and building plant all need servicing on a schedule — a compressor every 90 days, a forklift every 250 running hours, a fire alarm test every week. In many sites the schedule lives in a spreadsheet where someone types the next due date after each service and scans for anything red. That works until there are a few hundred tasks, some measured in days and some in hours of use, and someone has to answer two questions every Monday: what is overdue, and do we have enough technicians for what is coming? This page builds the schedule with pandas from a task list, the service history and the latest meter readings: next due dates for calendar-based and usage-based tasks, overdue jobs, every occurrence over the next twelve weeks, the weekly workload compared with the technicians' hours, and a grid in Excel with weeks across and assets down. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Three inputs. The task list names each maintenance task, whether its interval is in days or running hours, the interval, and how long the job takes. The service history gives the date each task was last done and, for usage-based tasks, the meter reading at that service. The meters table has each metered asset's current reading and its average running hours per day. Today is Monday 5 October 2026.

Step 1 — Next due date for every task

import pandas as pd

today = pd.Timestamp("2026-10-05")
tasks = pd.read_excel("maintenance_tasks.xlsx")
history = pd.read_excel("service_history.xlsx", parse_dates=["Last done"])
meters = pd.read_excel("meters.xlsx")

plan = tasks.merge(history, on=["Asset", "Task"], how="left").merge(meters, on="Asset", how="left")

by_days = plan["Interval type"] == "days"
plan.loc[by_days, "Next due"] = plan.loc[by_days, "Last done"] + pd.to_timedelta(plan.loc[by_days, "Interval"], unit="D")

by_hours = ~by_days
hours_left = plan["Meter at service"] + plan["Interval"] - plan["Meter now"]
plan.loc[by_hours, "Hours left"] = hours_left[by_hours]
plan.loc[by_hours, "Next due"] = today + pd.to_timedelta((hours_left / plan["Hours per day"])[by_hours].round(), unit="D")

plan["Days to due"] = (plan["Next due"] - today).dt.days
plan["Overdue"] = plan["Days to due"] < 0
print(plan[["Asset", "Task", "Interval type", "Hours left", "Next due", "Days to due", "Overdue"]]
      .assign(**{"Next due": plan["Next due"].dt.date}).to_string(index=False))

Calendar tasks are easy: last done plus the interval in days. Usage-based tasks need one more step. A forklift serviced at 3,410 hours on a 250-hour interval is next due at 3,660 hours; with the meter now at 3,620 it has 40 hours left, and at an average of 7.5 hours a day that is about five days — so the job is due on Saturday. The hours left figure is the real trigger and the date is an estimate based on recent usage, which is why the report shows both. Negative values mean a task is overdue: the second forklift has already run 15 hours past its Service A, the compressor's quarterly service is six days late and the HVAC filter clean eight. The first forklift's Service B falls due today, at exactly 1,000 hours since the last one. Assets that run more or less than usual move their due dates automatically when the next meter readings arrive.

Step 2 — Every occurrence over the next twelve weeks

horizon = today + pd.Timedelta(weeks=12)
rows = []
for t in plan.to_dict("records"):
    days = t["Interval"] if t["Interval type"] == "days" else round(t["Interval"] / t["Hours per day"])
    due = max(t["Next due"], today)                           # overdue jobs are scheduled for this week
    while due <= horizon:
        rows.append({"Asset": t["Asset"], "Asset name": t["Asset name"], "Task": t["Task"], "Due": due,
                     "Week": due.to_period("W-SUN").start_time.date(), "Hours": t["Hours to do"],
                     "Overdue": t["Overdue"] and due == today})
        due += pd.Timedelta(days=days)
jobs = pd.DataFrame(rows).sort_values("Due")
print(len(jobs), "jobs in 12 weeks;", jobs.groupby("Task").size().sort_values(ascending=False).head(4).to_dict())

A schedule is more useful when it shows not just the next occurrence but every one in the planning window. Starting from each task's next due date, the loop steps forward by the interval — in days for calendar tasks, and for usage tasks the interval converted to days at the average usage rate — until it passes the twelve-week horizon. Overdue jobs are pulled into the current week rather than shown in the past, since that is when they have to be done. Each job gets its week, so the workload can be summed per week. The weekly fire alarm test produces twelve of the 27 jobs on its own, which shows why frequent small tasks need to be in the plan even though each is quick.

Step 3 — Workload against technician hours

capacity = 1 * 37.5 * 0.2                                    # one technician, 20% of the week on planned maintenance
load = jobs.groupby("Week")["Hours"].sum().rename("Planned hours").to_frame()
load["Capacity"] = capacity
load["Over capacity"] = load["Planned hours"] > load["Capacity"]
print(load.head(6).to_string())
print("weeks over capacity:", [str(w) for w in load.index[load["Over capacity"]]])

Summing the job hours by week and comparing them with the time technicians have for planned work shows where the schedule is unrealistic before it fails. At this small site one technician works 37.5 hours a week, with a fifth of that set aside for planned maintenance and the rest for breakdowns and other work, which gives 7.5 hours a week. Most weeks need far less. The current week does not: it has to absorb three overdue jobs, the forklift's five-hour Service B that falls due today and the weekly test, 11.5 hours in all. That is the week to move a flexible job — the forklift's Service A, still forty running hours away, can wait a few days — or to bring in help. Seeing it on Monday morning, rather than discovering it on Thursday, is the point of the comparison.

Step 4 — The schedule workbook

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

grid = jobs.assign(Mark=jobs["Task"].str.split().str[0]).pivot_table(
    index=["Asset", "Asset name"], columns="Week", values="Mark", aggfunc=lambda s: ", ".join(sorted(set(s))))
path = "maintenance_schedule.xlsx"
with pd.ExcelWriter(path) as xw:
    plan.assign(**{"Next due": plan["Next due"].dt.date, "Last done": plan["Last done"].dt.date}).to_excel(xw, sheet_name="Tasks", index=False)
    jobs.assign(Due=jobs["Due"].dt.date).to_excel(xw, sheet_name="Jobs", index=False)
    load.reset_index().to_excel(xw, sheet_name="Workload", index=False)
    grid.to_excel(xw, sheet_name="Weekly grid")
wb = load_workbook(path)
ws = wb["Weekly grid"]
for row in ws.iter_rows(min_row=2, min_col=3):
    for cell in row:
        if cell.value:
            cell.fill = PatternFill("solid", fgColor="DDEBF7")
ws.freeze_panes = "C2"
wb.save(path)
print(grid.shape, "assets × weeks")

The workbook has four views of the same data: the task list with next due dates and overdue flags, the list of every job in the next twelve weeks, the weekly workload against capacity, and a grid with assets down the side and weeks across, each cell naming the jobs due that week. The grid is what most people look at — it shows at a glance when each machine will be out of service, which matters to production planners as much as to the maintenance team. When a job is done, the service history gets a new row and the next run moves everything forward; nothing in the schedule is typed by hand.

Two refinements are worth adding once the basic schedule is trusted. Usage rates drift — a forklift works harder in the run-up to Christmas — so calculate hours per day from the last few weeks of meter readings rather than a fixed figure. And some tasks should reset the clock of others: when Service B is done, it usually includes everything in Service A, so recording it should also count as the last Service A. Both are small additions to the history table rather than changes to the schedule logic.

Common pitfalls

• Usage tasks scheduled by calendar. Base them on hours left, and show the estimated date as an estimate.
• Overdue jobs left in the past. Bring them into the current week so they are planned.
• Only the next occurrence. Generate every job in the horizon so workload can be summed.
• Ignoring capacity. Compare weekly hours with the time technicians actually have.
• Stale meter readings. Usage-based dates are only as good as the latest readings.

Conclusion

A maintenance schedule needs the next due date for every task — last done plus the interval for calendar tasks, and for usage tasks the hours left divided by the average daily usage. From there, generate every occurrence in the planning window, bring overdue jobs into the current week, sum the job hours per week against technician capacity, and write a task list, a job list, a workload table and an asset-by-week grid to Excel.

Frequently asked questions

How do I calculate the next service date from running hours? Subtract the current meter reading from the reading at the last service plus the interval to get hours left, then divide by average daily usage to estimate the date.

How do I list every maintenance job in the next twelve weeks? Start from each task's next due date and add the interval repeatedly until the date passes the horizon.

How should overdue maintenance be scheduled? Put overdue jobs into the current week so they appear in this week's plan and workload.

How do I check maintenance workload against capacity? Sum the hours of the jobs due in each week and compare with the hours technicians have for planned work.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Build a Capacity Planning Report — load against capacity.
• Create a Gantt Chart in Excel with Python — another timeline view.
• Highlight Overdue Dates with openpyxl — flagging late jobs.