Define the due-work cohort before counting completions. For runtime, define the asset and planned interval before adding downtime. This original workbook specification makes both denominators visible and keeps the underlying records available for review.
Separate due-month timeliness from period-end completion
CloudMaint's public guidance defines preventive-maintenance compliance using an agreed completion window and due-work denominator. It also cautions against automatically treating late work as compliant or changing populations without explaining the rule. The example below uses a strict on-or-before-due-time rule. It is an original specification, not the vendor's downloaded workbook. CloudMaint's maintenance KPI guidance
The September 2026 cohort is P1–P4, selected by due time, with a snapshot at September 30, 2026, 23:59 UTC. To make the boundary explicit, these are original illustrative timestamps rather than inferred times from real date-only records:
| Task | Due timestamp, UTC | Completion timestamp, UTC | September cohort result |
|---|---|---|---|
| P1 | September 10, 17:00 | September 9, 16:00 | On time |
| P2 | September 15, 17:00 | September 17, 16:00 | Completed late |
| P3 | September 20, 17:00 | September 20, 16:00 | On time |
| P4 | September 30, 17:00 | Still open at snapshot | Not completed |
| P5 | October 5, 17:00 | September 29, 16:00 | Outside September due cohort |
Two of the four due tasks finished on time: 2/4 = 50%. Three finished by the period-end snapshot: 3/4 = 75%. Label the second result “Completed by period end,” not “On-time compliance.” P2 belongs in the completed count but not the on-time numerator. P5's September completion does not move its October due time into the September cohort.
Actual date-only records need the organization's precise due and completion boundaries confirmed before a same-day completion can be classified. Keep unresolved timing as TO CONFIRM; do not invent a timestamp in a maintained work order because the demonstration supplied one.
Clip and merge downtime before subtracting it
IBM describes runtime-based availability as runtime divided by scheduled production time. For this example, use a clearly named runtime fraction for one fictional asset and one declared interval. It is distinct from an inherent-availability estimate based on MTBF and MTTR, and it is not an OEE result. IBM's equipment-downtime metric guidance
Asset A's planned production interval is October 13, 2026, 09:00–17:00 UTC: 480 minutes. Use a start-inclusive, end-exclusive interval convention throughout. The supplied events are:
| Event | Recorded interval, UTC | Treatment for the 09:00–17:00 interval |
|---|---|---|
| Scheduled maintenance | 08:30–09:00 | Outside the planned interval; contributes zero minutes |
| D1, unplanned downtime | 10:00–10:30 | 30 raw minutes |
| D2, unplanned downtime | 10:20–11:00 | 40 raw minutes |
D1 and D2 overlap from 10:20 to 10:30. Their combined coverage is 10:00–11:00, or 60 minutes, not 70. Clip each event to the planned interval first, then merge overlaps for the same asset. Preserve the raw events so a reviewer can inspect why ten overlapping minutes were counted only once.
Under this fictional model, Asset A is either running or down during the planned interval, and the supplied downtime coverage is complete. Runtime is therefore 480 − 60 = 420 minutes. The runtime fraction is 420/480 = 87.5%. These assumptions are part of the result. Missing event coverage would prevent a claim that this was measured availability for a real asset.
Keep the Excel workbook as the maintained artifact
Specify five workbook areas: Definitions for metric scope and interval conventions; PM Records for task IDs, due/completion timestamps and evidence references; Downtime Events for asset/event IDs and raw intervals; Calculations for cohort membership and merged interval coverage; and Dashboard for labeled results and drill-down references.
The on-time rule checks that a task belongs to the due cohort and has a valid completion at or before its due time. Period-end completion separately checks the snapshot boundary. The runtime calculation uses the same asset and planned interval for both numerator and denominator. Keep raw intervals beside the merged-coverage output; a simple sum of event durations cannot handle this overlap.
For zero or unconfirmed planned minutes, report NOT CALCULABLE. Do not default to 100%. Invalid intervals and uncertain timestamps need visible review flags. Record any definition change before comparing periods. No equipment operation, repair, shutdown, or staff evaluation is specified by these measures.
Copy this prompt for the workbook specification
Create one complete, self-contained US-English HTML companion for “Maintenance KPI • calculation companion”. Return the full HTML document, with inline CSS and any JavaScript needed for the local interactions. Use the original scenario, named records, numbers and expected cases below.
Design the page for this task: use clearly labeled input or source records, a readable result or worked example, and an adjacent explanation of the evidence. Use a restrained visual hierarchy, left-aligned text, readable tables or stacked records, and a responsive layout at 720px and 360px. Provide meaningful labeled controls for Show raw intervals; Show merged interval; Restore 09:00–17:00; Select text. Use keyboard-accessible native controls and visible focus. Keep the complete content readable without an external dependency.
The following brief supplies the substantive content and constraints. Native-format requests describe the material to represent or copy manually; this HTML must not pretend to create DOCX, XLSX, PDF, PPTX, H5P or another native file, run Python/Flask/Django/Snowflake/Power BI, connect a live service, send messages, or perform an authorized real-world action. Where the task requires a native source, actual observation or separate approval, retain that requirement and leave unsupplied evidence blank.
Create a complete US English Excel workbook specification for two maintenance KPI reviews, with original source tables,field definitions,calculation rules,expected outputs and an evidence checklist. Do not claim to have created or tested an XLSX. PM cohort:September2026 due work P1-P4;snapshot September30,2026 23:59UTC. Strict completion at/before due time. Original illustrative UTC timestamps:
P1dueSep10 17:00,completeSep9 16:00;P2dueSep15 17:00,completeSep17 16:00;P3dueSep20 17:00,completeSep20 16:00;P4dueSep30 17:00,stillopen;P5dueOct5 17:00,completeSep29 16:00,outsideSeptembercohort. On-time2/4=50%;completedbyperiodend3/4=75%,distinct labels. P2late notontime;P5notSeptemberdue. Real date-only boundaries stayTOCONFIRM,not inferred from sampletimes. AssetA plannedproduction October13,2026 09:00-17:00UTC=480minutes. Startinclusive,endexclusive. Scheduledmaintenance08:30-09:00outside contributes0. D1 10:00-10:30,D2 10:20-11:00:raw70minutes,overlap10,union60. Clip to sameasset/plannedinterval thenmerge. Exclusive running/down model with complete supplied coverage givesruntime420minutes and420/480=87.5%. Label fictional;missingcoverage prevents realmeasuredavailability claim. NotMTBF/(MTBF+MTTR),OEE orcausation. Workbook areas:Definitions,PMRecords,DowntimeEvents,Calculations,Dashboard. Preserve task/asset/event IDs,rawtimestamps,evidence references,cohort membership andmergedcoverage trace. Zero/unconfirmedplannedminutes NOTCALCULABLE,not100%;flag invalid intervals/uncertaintimestamps. NativeExcel retained. Any optionalHTML review views only illustrate cohort/raw-versus-merged logic;construction/testingpending,no sensor/CMMS sync. No equipment operation/repair/safety instructions orfinancial/staff scores.
COMPLETE AUTHORED SCENARIO AND EXPECTED CASES
The following is the supplied reading/teaching/calculation record to represent in the companion. Preserve its particular content, examples, source references and unknowns. Wording about a future native implementation refers to that native task, not permission to omit this HTML companion.
# A Maintenance KPI Dashboard Excel Template with Denominators You Can Trace
A maintenance task was due in September and finished two days late. Another was due in October but finished during September. Counting both as September successes can make a dashboard look better while changing the question it answers.
Define the due-work cohort before counting completions. For runtime, define the asset and planned interval before adding downtime. This original workbook specification makes both denominators visible and keeps the underlying records available for review.
## Separate due-month timeliness from period-end completion
CloudMaint's public guidance defines preventive-maintenance compliance using an agreed completion window and due-work denominator. It also cautions against automatically treating late work as compliant or changing populations without explaining the rule. The example below uses a strict on-or-before-due-time rule. It is an original specification, not the vendor's downloaded workbook. [CloudMaint's maintenance KPI guidance](https://cloudmaint.com/maintenance-kpi-dashboard-template)
The September 2026 cohort is P1–P4, selected by due time, with a snapshot at September 30, 2026, 23:59 UTC. To make the boundary explicit, these are original illustrative timestamps rather than inferred times from real date-only records:
| Task | Due timestamp, UTC | Completion timestamp, UTC | September cohort result |
| --- | --- | --- | --- |
| P1 | September 10, 17:00 | September 9, 16:00 | On time |
| P2 | September 15, 17:00 | September 17, 16:00 | Completed late |
| P3 | September 20, 17:00 | September 20, 16:00 | On time |
| P4 | September 30, 17:00 | Still open at snapshot | Not completed |
| P5 | October 5, 17:00 | September 29, 16:00 | Outside September due cohort |
Two of the four due tasks finished on time: `2/4 = 50%`. Three finished by the period-end snapshot: `3/4 = 75%`. Label the second result “Completed by period end,” not “On-time compliance.” P2 belongs in the completed count but not the on-time numerator. P5's September completion does not move its October due time into the September cohort.
Actual date-only records need the organization's precise due and completion boundaries confirmed before a same-day completion can be classified. Keep unresolved timing as `TO CONFIRM`; do not invent a timestamp in a maintained work order because the demonstration supplied one.
## Clip and merge downtime before subtracting it
IBM describes runtime-based availability as runtime divided by scheduled production time. For this example, use a clearly named runtime fraction for one fictional asset and one declared interval. It is distinct from an inherent-availability estimate based on MTBF and MTTR, and it is not an OEE result. [IBM's equipment-downtime metric guidance](https://www.ibm.com/think/topics/reduce-equipment-downtime)
Asset A's planned production interval is October 13, 2026, 09:00–17:00 UTC: 480 minutes. Use a start-inclusive, end-exclusive interval convention throughout. The supplied events are:
| Event | Recorded interval, UTC | Treatment for the 09:00–17:00 interval |
| --- | --- | --- |
| Scheduled maintenance | 08:30–09:00 | Outside the planned interval; contributes zero minutes |
| D1, unplanned downtime | 10:00–10:30 | 30 raw minutes |
| D2, unplanned downtime | 10:20–11:00 | 40 raw minutes |
D1 and D2 overlap from 10:20 to 10:30. Their combined coverage is 10:00–11:00, or 60 minutes, not 70. Clip each event to the planned interval first, then merge overlaps for the same asset. Preserve the raw events so a reviewer can inspect why ten overlapping minutes were counted only once.
Under this fictional model, Asset A is either running or down during the planned interval, and the supplied downtime coverage is complete. Runtime is therefore `480 − 60 = 420` minutes. The runtime fraction is `420/480 = 87.5%`. These assumptions are part of the result. Missing event coverage would prevent a claim that this was measured availability for a real asset.
## Keep the Excel workbook as the maintained artifact
Specify five workbook areas: Definitions for metric scope and interval conventions; PM Records for task IDs, due/completion timestamps and evidence references; Downtime Events for asset/event IDs and raw intervals; Calculations for cohort membership and merged interval coverage; and Dashboard for labeled results and drill-down references.
The on-time rule checks that a task belongs to the due cohort and has a valid completion at or before its due time. Period-end completion separately checks the snapshot boundary. The runtime calculation uses the same asset and planned interval for both numerator and denominator. Keep raw intervals beside the merged-coverage output; a simple sum of event durations cannot handle this overlap.
For zero or unconfirmed planned minutes, report `NOT CALCULABLE`. Do not default to 100%. Invalid intervals and uncertain timestamps need visible review flags. Record any definition change before comparing periods. No equipment operation, repair, shutdown, or staff evaluation is specified by these measures.
Implement the local companion rather than returning a text-only plan. Preserve every numerical fixture and the distinction between illustrative expectations and actual evidence. Any native-runtime or classroom tests mentioned above remain tests to perform in the proper environment; do not mark them passed. Keep edits and practice responses in this open page only, without claiming persistent storage. Provide invalid/missing-input feedback and reset behavior that agree with this specific brief. Do not claim accessibility certification, learning gains, real approvals or test results that were not observed.
Review the definitions
Use the HTML companion to inspect whether P2 and P5 are classified correctly and whether the overlap stays visible in the companion. Keep the maintained Excel workbook and actual maintenance evidence in their native workflow.
Finish with 50% on-time work, 75% period-end completion, and the conditional 87.5% runtime fraction labeled separately, each traceable to the records and boundaries that produced it.