Training completion by department as of a selected date

A reporting-date snapshot of training assignments

Each row represents one training assignment. Due dates and the reporting date are required numeric whole-day dates; the completion date may be blank. This example accepts dates from March 1, 1900 through December 31, 9999 in the workbook date system. Text dates, zero and fractional-day timestamps are rejected.

A completion after the reporting date has not happened yet in that snapshot. Until then, the assignment is overdue only when the reporting date is strictly after its due date; it stays in progress on the due date itself. A completion on the due date counts as on time.

These labels summarize recorded assignments, not certification or regulatory compliance. Multiple assignments per person count as separate rows; the percentage is not a unique-person completion rate. Historical snapshots also assume the recorded due date and department were applicable on that date; this sheet does not retain assignment-change history.

Classify each assignment at the reporting date

Copy the row formula down from E2. Absolute reference $G$2 keeps the reporting date fixed. Invalid dates receive a separate label rather than being classified as finished or overdue.

=IF(AND(ISNUMBER(C2),ISNUMBER($G$2)),IF(AND(AND(C2>=DATE(1900,3,1),C2<=DATE(9999,12,31),C2=INT(C2)),AND($G$2>=DATE(1900,3,1),$G$2<=DATE(9999,12,31),$G$2=INT($G$2))),IF(COUNTBLANK(D2)=1,IF($G$2>C2,"Overdue","In Progress"),IF(ISNUMBER(D2),IF(AND(D2>=DATE(1900,3,1),D2<=DATE(9999,12,31),D2=INT(D2)),IF(D2>$G$2,IF($G$2>C2,"Overdue","In Progress"),IF(D2<=C2,"Completed","Completed Late")),"Invalid dates"),"Invalid dates")),"Invalid dates"),"Invalid dates")

Department total completion rate (including Completed + Completed Late)

The numerator includes both completion labels; the denominator counts all assignments for the literal department in G3. West* would be a literal label, not a wildcard. Eng has three completed assignments out of four (0.75); format this fraction as a percentage.

No matching department returns No assignments rather than division by zero. This fixed five-row log requires all departments and dates to be valid before either percentage is reported; fix incomplete records instead of silently removing them.

=IF(OR(COUNTBLANK(B2:B6)>0,COUNTBLANK(G3)>0,COUNTIF(E2:E6,"Invalid dates")>0),"Invalid inputs",IF(SUMPRODUCT((B2:B6=G3)*1)=0,"No assignments",ROUND(SUMPRODUCT((B2:B6=G3)*((E2:E6="Completed")+(E2:E6="Completed Late")))/SUMPRODUCT((B2:B6=G3)*1),4)))

Further reading

• Microsoft Support — COUNTIFS function