How to track employee certification expiration dates

Step 1: List what each role needs

Before tracking dates, write down which credentials each job requires. A security officer might need a state guard license and a firearm permit. A warehouse lead might need forklift certification, first aid, and CPR. This list is usually called a training matrix: roles down the side, credentials across the top.

The matrix tells you what is missing, which a list of dates cannot. If a new hire has no row for a required credential, nothing will ever remind you about it. You can build one with our free training matrix generator and use it as the starting point for the steps below.

Step 2: Record one row per person per credential

Keep it flat. One row for each credential each person holds, with these columns:

Step 3: Work out the right "next due" date

This is where most homemade trackers go wrong. Credentials fall into three groups.

The card shows an expiration date

State guard licenses are the usual example. Copy the date from the card, and check the agency's license lookup if the card is old or hard to read.

A rule sets the date, not the card

Some requirements run on a schedule from the date of training or evaluation, and the certificate may not say so. Two federal examples:

• Forklift operators. OSHA requires an evaluation of each operator's performance at least once every three years, and the employer certifies the training and evaluation (29 CFR 1910.178(l)(4)(iii) and (l)(6)). Next due is the evaluation date plus three years, or sooner if something triggers refresher training.
• HAZWOPER workers. Covered employees and supervisors need eight hours of refresher training every year (29 CFR 1910.120(e)(8)). Next due is the last refresher date plus 12 months.

No federal expiration, but a local one

OSHA Outreach cards (OSHA 10 and OSHA 30) do not expire under federal rules, according to OSHA's Outreach Training Program FAQ. Some states, cities, and customers set their own limits, which our OSHA card guide covers. If a rule like that applies to your crew, record the date it sets. If none applies, leave next due blank so the card does not trigger false alarms.

Step 4: Set reminder lead times

A 30-day reminder is too late for anything that needs a class, a range date, or agency processing. Work backward from the slowest step:

• 90 days: book training, testing, or qualification that has limited seats.
• 60 days: confirm the person has started the renewal application, if one is needed.
• 30 days: chase anything still open and plan coverage in case it lapses.
• On the date: pull the person from work that requires the credential until proof arrives.

Some agencies open renewal earlier. Texas, for example, lets security licenses renew starting 180 days before expiration (Texas DPS FAQ). Match your first reminder to the earliest useful date.

Step 5: Collect proof, not promises

"I renewed it last week" does not belong in an audit file. Ask for a photo of the new card or the completion certificate, attach it to the row, and only then change the next due date. Updating the date without proof is how trackers drift away from reality.

Step 6: Review weekly

Pick a fixed time each week. Sort by next due and look at four groups: expired, due within 30 days, due within 60, and due within 90. Ten minutes is usually enough for a roster of under a hundred people.

A spreadsheet setup that works

These formulas work in both Excel and Google Sheets. Assume column D is the completed date, column E is next due, and column F is days left.

• Next due, three-year rule: =EDATE(D2, 36)
• Next due, annual rule: =EDATE(D2, 12)
• Days left: =IF(E2="", "", E2-TODAY())
• Status: =IF(F2="", "No expiry", IF(F2<0, "Expired", IF(F2<=30, "Due in 30", IF(F2<=90, "Due in 90", "OK"))))

Add conditional formatting on the status column so expired rows turn red and "Due in 30" turns amber. Then freeze the header row, sort by days left, and you have a working dashboard.

The weak point is the one no formula fixes: the sheet only helps when someone opens it, and the reminders depend on that person remembering to look.