How do I turn my injury log and hours worked into rolling 12-month TRIR and DART safety rates by site and subcontractor with AI?
Monthly safety rates go wrong in the same few places: hours pulled from the wrong report, salaried and temporary workers handled differently each month, cases counted in the wrong month after they change, and a plain average of monthly rates reported as the year's rate. Keep one cases tab (from your 300-style log, one row per case) and one hours tab (by month, site and employer), have Copilot in Excel or Gemini in Google Sheets build rolling 12-month TRIR and DART rates by site and subcontractor from totals, and have Claude or ChatGPT write a plain-language monthly summary that sticks to the numbers. Whether a case is recordable is decided by you under 29 CFR 1904, not by the AI.
Safety Manager · Construction · Microsoft Excel · Microsoft Copilot · Google Sheets · Claude · ChatGPT
Content updated · Tool links listed below; vendor features and pricing change, so confirm with the vendor
Short answer
Use Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT: Copilot or Gemini builds a monthly table from your cases and hours tabs with rolling 12-month TRIR and DART by site and subcontractor, computed from 12-month totals with the base hours you enter, plus flags for missing hours; Claude or ChatGPT writes a short factual summary of what moved. Monthly safety rates go wrong in the same few places: hours pulled from the wrong report, salaried and temporary workers handled differently each month, cases counted in the wrong month after they change, and a plain average of monthly rates reported as the year's rate. Keep one cases tab (from your 300-style log, one row per case) and one hours tab (by month, site and employer), have Copilot in Excel or Gemini in Google Sheets build rolling 12-month TRIR and DART rates by site and subcontractor from totals, and have Claude or ChatGPT write a plain-language monthly summary that sticks to the numbers. Whether a case is recordable is decided by you under 29 CFR 1904, not by the AI. A person still checks: every case's classification and month against your log, the hours against payroll or certified subcontractor hours, and a hand check of one rolling rate; recordability decisions stay with the responsible safety professional under 29 CFR 1904.
AI tie-in
Tool: Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT
Copilot or Gemini builds a monthly table from your cases and hours tabs with rolling 12-month TRIR and DART by site and subcontractor, computed from 12-month totals with the base hours you enter, plus flags for missing hours; Claude or ChatGPT writes a short factual summary of what moved.
Prompt 1
Here are the headers and a few sample rows of my Cases tab [paste: case number, injury month, site, employer, case type, days away, days restricted; no names or medical details] and my Hours tab [paste: month, site, employer, hours worked]. Settings!B1 holds the base hours I normalize to and Settings!B2 my target rate. Write Excel formulas (or a step-by-step Copilot plan) that build a table by month, site and employer with: cases by type, hours, recordable count, DART count (days away plus restricted or transfer cases), and rolling 12-month TRIR and DART computed as 12-month cases times the base divided by 12-month hours. Do not average monthly rates. Add CHECK where hours are blank or zero, where a site has cases but no hours, and where fewer than 12 months are available. Use only my data and my settings; do not supply any value from memory.
Prompt 2
Here is this month's rate table [paste site, employer, 12-month hours, 12-month recordable and DART counts, rolling TRIR and DART, last month's values and the CHECK flags]. Write a short summary for our monthly safety meeting: which sites and employers moved up or down and by how much, how many hours sit behind each rate, and which rows have CHECK flags that need fixing before the numbers are shared. Stick to the numbers in the table. Do not classify any case, do not compare us to any industry average, and do not say whether any program is adequate.
Human check: Every case's classification and month against your log, the hours against payroll or certified subcontractor hours, and a hand check of one rolling rate; recordability decisions stay with the responsible safety professional under 29 CFR 1904.
The problem
Turn your injury log and hours worked into rolling 12-month TRIR and DART safety rates by site and subcontractor with AI. This is a common, time-consuming task for a safety manager.
What this workflow does
Monthly safety rates go wrong in the same few places: hours pulled from the wrong report, salaried and temporary workers handled differently each month, cases counted in the wrong month after they change, and a plain average of monthly rates reported as the year's rate. Keep one cases tab (from your 300-style log, one row per case) and one hours tab (by month, site and employer), have Copilot in Excel or Gemini in Google Sheets build rolling 12-month TRIR and DART rates by site and subcontractor from totals, and have Claude or ChatGPT write a plain-language monthly summary that sticks to the numbers. Whether a case is recordable is decided by you under 29 CFR 1904, not by the AI.
How it works: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.
Your checkpoint: The workbook does arithmetic on the cases and hours you enter. It does not decide whether a case is work-related or recordable, how it should be classified, or whether your program is adequate. Case classification, hours methodology and anything filed or reported stay with the safety professional responsible for the log, checked against 29 CFR 1904 and your company's procedures.
Before you start
- Access to the named tool; confirm license, version and cost with the vendor.
- A small, non-confidential sample to try the workflow on first.
- A qualified person to review the output before it is used.
Tools and access
Follow the steps
- Set up a Cases tab with one row per case: case number, injury date, site, employer (your company or which subcontractor), case type as you classified it on your log (days away, restricted or transfer, other recordable), days away and days restricted. Leave out employee names and medical details before anything goes into an AI tool.
- Set up an Hours tab with one row per month, site and employer: hours actually worked, from payroll or the subcontractor's certified hours. Write down once how you count salaried and temporary workers, following the hours guidance in your recordkeeping references, and use it every month.
- Type the normalizing base hours you report against (the US convention is 200,000, about 100 full-time workers for a year) into a Settings cell, along with any targets your company sets.
- Have Copilot in Excel or Gemini in Google Sheets build a monthly table by site and employer with cases and hours, and rolling 12-month TRIR and DART computed from 12-month case and hour totals, not by averaging monthly rates. Add CHECK flags for months with no hours entered, cases at a site with no hours that month, and rolling windows with fewer than 12 months of data.
- When a case changes type or gains days, change it in its original injury month and let the rolling rates recompute. Spot-check one site's rolling rate by hand each quarter.
- Paste the monthly table into Claude or ChatGPT and have it write a short factual summary for the safety meeting: which sites and employers moved, by how much, and how many hours sit behind each rate. Pair it with your near-miss and observation trends rather than reading the rate alone.
Try a small example
Replace the bracketed fields with your own project details. No tested sample run is published for this workflow yet.
Sample input
Fill in the bracketed [fields] in the text below with your project data.
Starter prompt
Here are the headers and a few sample rows of my Cases tab [paste: case number, injury month, site, employer, case type, days away, days restricted; no names or medical details] and my Hours tab [paste: month, site, employer, hours worked]. Settings!B1 holds the base hours I normalize to and Settings!B2 my target rate. Write Excel formulas (or a step-by-step Copilot plan) that build a table by month, site and employer with: cases by type, hours, recordable count, DART count (days away plus restricted or transfer cases), and rolling 12-month TRIR and DART computed as 12-month cases times the base divided by 12-month hours. Do not average monthly rates. Add CHECK where hours are blank or zero, where a site has cases but no hours, and where fewer than 12 months are available. Use only my data and my settings; do not supply any value from memory.
Expected output
Not published yet. Treat any output as a draft and check it against the source documents.
Check the result
- Every value, citation or quantity traces back to a source document.
- Missing information is marked as missing, not filled in.
Limits and recovery
- The workbook does arithmetic on the cases and hours you enter. It does not decide whether a case is work-related or recordable, how it should be classified, or whether your program is adequate. Case classification, hours methodology and anything filed or reported stay with the safety professional responsible for the log, checked against 29 CFR 1904 and your company's procedures.
- Check your organization’s data policy before sharing project files with third-party tools.
Stop or switch when: The source cannot be verified, permissions are missing, or the result contains unsupported facts.
Frequently asked questions
How does AI help with this?
Copilot in Excel or Gemini in Google Sheets, then Claude or ChatGPT: Copilot or Gemini builds a monthly table from your cases and hours tabs with rolling 12-month TRIR and DART by site and subcontractor, computed from 12-month totals with the base hours you enter, plus flags for missing hours; Claude or ChatGPT writes a short factual summary of what moved. A person still checks: Every case's classification and month against your log, the hours against payroll or certified subcontractor hours, and a hand check of one rolling rate; recordability decisions stay with the responsible safety professional under 29 CFR 1904.
How do I how do I turn my injury log and hours worked into rolling 12-month TRIR and DART safety rates by site and subcontractor with AI??
Set up a Cases tab with one row per case: case number, injury date, site, employer (your company or which subcontractor), case type as you classified it on your log (days away, restricted or transfer, other recordable), days away and days restricted. Leave out employee names and medical details before anything goes into an AI tool. Set up an Hours tab with one row per month, site and employer: hours actually worked, from payroll or the subcontractor's certified hours. Write down once how you count salaried and temporary workers, following the hours guidance in your recordkeeping references, and use it every month. Type the normalizing base hours you report against (the US convention is 200,000, about 100 full-time workers for a year) into a Settings cell, along with any targets your company sets. The full step list is above.
Does this use an AI agent or regular software?
It is labeled AI assists: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.
What tools do I need?
Microsoft Excel, Microsoft Copilot, Google Sheets, Claude, ChatGPT. Confirm license, version and cost with the vendor, and try it on a small non-confidential sample first.
Has this workflow been tested?
Workflow guide · not independently tested. Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.
Evidence and maintenance
Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.