Guide · Updated 2026-10-02
How to Fix Date Format Errors in a Training Matrix
If some cells say “03/04/2026”, some say “April 26” and some are text, your expiring filter is fiction. Convert everything to true date values, pick one display format, and re-check a sample against certificates.
Quick answer
In Excel, ensure cells are Date type (not Text), normalise day/month order for your locale, replace vague month-year entries with a real day from the certificate, then sort and filter to confirm expiries order correctly.
Common failure modes
- UK/US day-month swaps (04/03 vs 03/04)
- Dates pasted as text from emails
- “March 2026” with no day — useless for gate planning
- Two-digit years and ambiguous centuries
- Merged cells breaking sort
Cleanup sequence
- Copy the live sheet to an archive snapshot first.
- Select date columns and set a single format (prefer YYYY-MM-DD for clarity).
- Use Excel’s text-to-columns or VALUE carefully on text dates — spot-check swaps.
- Open a sample of certificates and confirm the matrix matches the printed expiry.
- Re-run expiring filters and fix outliers.
After cleanup, ban free-text dates in the updating procedure. If someone only knows the month, leave the cell blank and log a gap until the card is read.
Honest limits
FieldClear stores structured dates so status does not depend on spreadsheet formatting. Migrating messy Excel still needs a human check against evidence. Not legal advice.
Already have a spreadsheet?
Upload your existing matrix and see who’s current, what’s expiring and what’s missing.
14-day free trial. No card required.