Challenge Scenario
In healthcare, engineering, and IT operations, maintaining active professional credentials and safety certifications is mandatory. Using the reference audit date in $G$1 (2026-09-01), compliance managers track validity windows:
- Expired (
C2 < 0): Marked as "EXPIRED".
- Expiring in 30 days or fewer (
C2 <= 30): Marked as "RENEW SOON".
- More than 30 days: Marked as
"ACTIVE".
As a Corporate Compliance Officer, you are auditing the staff certification registry:
- Days Remaining (Column C): Subtract the audit date from the credential expiry date:
=B2-$G$1.
- Compliance Status (Column D): Classify using nested IF:
=IF(C2<0, "EXPIRED", IF(C2<=30, "RENEW SOON", "ACTIVE")).
- Total Expired Certifications (Cell C8): Count expired credentials using
COUNTIF(D2:D6, "EXPIRED").
- Total Urgent Renewals (Cell D8): Count credentials needing renewal soon using
COUNTIF(D2:D6, "RENEW SOON").
Your Sheet Layout
Understanding the certification audit sheet:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B6 |
Employee & Cert, Expiry Date |
Credential Data |
Professional certificate titles and expiration dates |
Read-only certification registry. |
G1 |
Audit Date |
Anchor Date |
Locked reference date: 2026-09-01 |
Reference date for days calculation. |
C2:C6 |
Days Left |
Validity Window |
=B2-$G$1 |
Days until expiration (negative = past due). |
D2:D6 |
Status |
Alert Zone |
=IF(C2<0, "EXPIRED", IF(C2<=30, "RENEW SOON", "ACTIVE")) |
Compliance flag (EXPIRED, RENEW SOON, ACTIVE). |
C8 |
Expired Count |
Summary |
=COUNTIF(D2:D6, "EXPIRED") |
Total non-compliant certifications (2). |
D8 |
Renew Soon Count |
Summary |
=COUNTIF(D2:D6, "RENEW SOON") |
Total upcoming renewals within 30 days (1). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells C2:C6, calculate days remaining (= Expiry Date - $G$1).
2
In cells D2:D6, assign status ("EXPIRED" if <0, "RENEW SOON" if <=30, otherwise "ACTIVE").
3
In cell C8, count total certifications that are "EXPIRED".
4
In cell D8, count total certifications that need to "RENEW SOON".
Solution & Formula Breakdown
Let's examine how expiry math and tiered IF status rules work:
1. Calculating Days Remaining (C2:C6)
=B2-$G$1
- Jordan (AWS): 2026-08-15 minus 2026-09-01 = -17 days (past due!).
- Priya (Scrum): 2026-09-20 minus 2026-09-01 = +19 days.
- Kevin (CISSP): 2027-04-10 minus 2026-09-01 = +221 days.
- Hannah (CPR): 2026-07-30 minus 2026-09-01 = -33 days (past due!).
- Liam (Six Sigma): 2026-11-15 minus 2026-09-01 = +75 days.
2. Status Classification with Nested IF (D2:D6)
=IF(C2<0, "EXPIRED", IF(C2<=30, "RENEW SOON", "ACTIVE"))
- Jordan (-17 days): < 0 → EXPIRED.
- Priya (+19 days): <= 30 → RENEW SOON.
- Kevin (+221 days): > 30 → ACTIVE.
- Hannah (-33 days): < 0 → EXPIRED.
- Liam (+75 days): > 30 → ACTIVE.
3. Audit Summary Counts (C8 & D8)
- Cell C8 (Expired Count):
=COUNTIF(D2:D6, "EXPIRED") → 2.
- Cell D8 (Renew Soon Count):
=COUNTIF(D2:D6, "RENEW SOON") → 1.
Subtracting an earlier date from a future date yields positive days, while subtracting from a past date yields negative days. This makes testing C2 < 0 the cleanest way to catch overdue items!