How do I find employees missing required training?

Create one row per employee and required course, look up the latest completion date with MAXIFS, and flag rows with no completion.

How do I calculate a certification expiry date in Excel?

Use EDATE(completion date, validity in months).

How do I find training records for leavers?

Filter training records whose employee ID doesn't appear in the current HR list.

How do I show compliance percentage per department?

Pivot the status table with department in rows and status in columns, then divide OK by the total for each department.

How do I handle courses that never expire?

Leave Valid months empty and treat an empty expiry as OK in the Status formula.

How do I give new starters time before they count as missing?

Add a due date based on start date plus your grace period, and only mark the course Missing once the due date has passed.

Can I do this in Excel 2016?

MAXIFS isn't available there; use a pivot table of the latest completion date per employee and course, or MAX(IF()) entered as an array formula.

What if training records use names instead of employee IDs?

Add the employee ID from the HR list first, matching on name and department, review any duplicates by hand, then match on the ID.

How do I report compliance to managers each month?

Refresh the exports, let the status formulas recalculate against today's date, and send each manager the Missing, Expired and Expiring soon rows for their team.

How to match an employee list to training records

By the Analistable team · Updated 2026-10-07 · 6 min read

Join the HR list (current employees) to the training log on employee ID, per required course. Employees with no completion are missing; completions with an expiry date before today are expired, and those expiring in the next 30 days need booking. Training records for people not on the HR list are usually leavers who should be archived.

Part of our guide: How to join spreadsheets on a common column

One row per employee and required course

Cross the employee list with the courses each role requires (a small Requirements table: role → course), then look up the latest completion:

Completed =MAXIFS(Training[Completed on], Training[Employee ID], [@[Employee ID]], Training[Course], [@Course])
Expires   =IF([@Completed] = 0, "", EDATE([@Completed], [@[Valid months]]))
Status    =IF([@Completed] = 0, "Missing",
           IF([@Expires] < TODAY(), "Expired",
           IF([@Expires] < TODAY() + 30, "Expiring soon", "OK")))

MAXIFS returns 0 when there's no record, which the Status formula treats as Missing.

Worked example (today: 7 October 2026)

Compliance status
Employee	Course	Completed	Valid (months)	Expires	Status
E-104	Fire safety	2025-11-20	12	2026-11-20	OK
E-117	Fire safety	2025-10-25	12	2026-10-25	Expiring soon
E-121	First aid	2023-06-10	36	2026-06-10	Expired
E-130	Fire safety	—	12		Missing

Summaries for managers

Pivot the status table by department and status to get completion percentages, or filter Expiring soon and sort by date to plan sessions. Training records whose employee ID has a COUNTIF of zero on the HR list belong to leavers.

Headcount comparisons (HR list vs payroll) use the same check — see reconciling payroll with timesheets.

Building the requirements grid

If every employee needs every course, cross the two lists in Microsoft 365 with:

=LET(e, Employees[Employee ID], c, Courses[Course],
     HSTACK(TOCOL(IF(SEQUENCE(1, ROWS(c)), e)), TOCOL(IF(SEQUENCE(ROWS(e)), TRANSPOSE(c)))))

This returns one row per employee and course. Where requirements depend on role, join employees to a Requirements table on role instead.

Common problems

Course names that changed over time (“Fire Safety 2024” vs “Fire safety”) — map them to one name.
Completion dates stored as text in exports from learning platforms — convert before MAXIFS.
Contractors missing from the HR list but present in training records — decide whether they're in scope.

Step by step

Export the current employee list from HR with employee ID, name, department, role and start date.
Export completions from the learning platform with employee ID, course and completion date. Field names vary between platforms; you need those three at minimum.
Build a Requirements table: role, course, validity in months.
Create one row per employee and required course (the grid section shows how).
Add Completed, Expires and Status with the formulas above, and pivot by department.

MAXIFS needs Excel 2019 or later; in Excel 2016, use an array formula with MAX(IF()) or a pivot table of the maximum completion date per employee and course. In Google Sheets, MAXIFS, EDATE and TODAY work the same way. If the learning platform exports names rather than IDs, add the employee ID from the HR list first, because names repeat and change.

Second example: new starters and grace periods

New starters usually get a set number of days to complete induction courses, so flagging them as Missing on day one creates noise. Add a Due date based on start date:

Due    =[@[Start date]] + 30
Status =IF([@Completed] = 0,
          IF([@Due] >= TODAY(), "Due by " & TEXT([@Due], "d mmm"), "Missing"),
          IF([@Expires] < TODAY(), "Expired",
          IF([@Expires] < TODAY() + 30, "Expiring soon", "OK")))

New starters (today: 7 October 2026)
Employee	Start date	Course	Due	Completed	Status
E-140	2026-09-28	Fire safety	2026-10-28	—	Due by 28 Oct
E-138	2026-08-25	Fire safety	2026-09-24	—	Missing
E-139	2026-09-01	Fire safety	2026-10-01	2026-09-15	OK

E-138 has passed the 30-day window and is now genuinely missing; E-140 still has three weeks. Adjust the grace period to your policy.

Troubleshooting

When statuses look wrong
Symptom	Likely cause	Fix
Completed employees show Missing	Employee IDs with leading zeros lost in one export	Store IDs as text on both sides
Expires shows a number	Cell formatted as General	Format the column as a date
Everyone shows Expired	Validity in years entered where months expected	Check the Valid months column
Courses missing from the grid	Requirements table spelled differently from the course names	Map course names to one spelling first

To check the result, the number of status rows should equal employees multiplied by the number of required courses for their role, and each status count in the pivot should add up to that total. Repeat monthly with fresh exports; the formulas recalculate against TODAY(), so the same file shows next month's expiries without changes. The lookup method is covered in pulling data from multiple sheets into one.

Doing it in SQL

With employees, requirements and completions as tables, one query gives the status for every required course. It runs in DuckDB; in SQLite, replace the date arithmetic with date(completed, '+' || valid_months || ' months'):

SELECT e.employee_id, r.course,
       MAX(t.completed_on) AS completed,
       CASE WHEN MAX(t.completed_on) IS NULL THEN 'Missing'
            WHEN MAX(t.completed_on) + to_months(r.valid_months) < current_date THEN 'Expired'
            ELSE 'OK' END AS status
FROM employees e
JOIN requirements r ON r.role = e.role
LEFT JOIN training t ON t.employee_id = e.employee_id AND t.course = r.course
GROUP BY e.employee_id, r.course, r.valid_months;

The LEFT JOIN keeps employees with no completion, and putting the course condition in the ON clause rather than WHERE stops the join from dropping them.

Common mistakes

Using the first completion instead of the latest, so renewed certificates still look expired.
Including leavers from an old HR export, which inflates the Missing count.
Counting optional courses in the compliance percentage.
Forgetting staff on long-term leave, who may be exempt until they return.

Which method when

Small team, one or two courses: COUNTIFS and MAXIFS on the HR list are enough.
Role-based requirements: the requirements grid with the formulas above.
Several sites or learning platforms: stack the training exports first, then use SQL or Power Query.

Whatever the method, keep the requirements table as the single place where courses and validity periods are defined, so a policy change is one edit rather than a search through formulas.

Frequently asked questions

How do I find employees missing required training?: Create one row per employee and required course, look up the latest completion date with MAXIFS, and flag rows with no completion.
How do I calculate a certification expiry date in Excel?: Use EDATE(completion date, validity in months).
How do I find training records for leavers?: Filter training records whose employee ID doesn't appear in the current HR list.
How do I show compliance percentage per department?: Pivot the status table with department in rows and status in columns, then divide OK by the total for each department.
How do I handle courses that never expire?: Leave Valid months empty and treat an empty expiry as OK in the Status formula.
How do I give new starters time before they count as missing?: Add a due date based on start date plus your grace period, and only mark the course Missing once the due date has passed.
Can I do this in Excel 2016?: MAXIFS isn't available there; use a pivot table of the latest completion date per employee and course, or MAX(IF()) entered as an array formula.
What if training records use names instead of employee IDs?: Add the employee ID from the HR list first, matching on name and department, review any duplicates by hand, then match on the ID.
How do I report compliance to managers each month?: Refresh the exports, let the status formulas recalculate against today's date, and send each manager the Missing, Expired and Expiring soon rows for their team.