What Goes Wrong in a 401(k) Census File, and How to Catch It Before It Reaches the TPA

I spent 16 years at NRS and Ascensus (NRS was acquired by Ascensus) and was in charge of the year-end census. It came in every way you can think of: CSV and Excel files, over the phone, on paper. When it was a CSV or an Excel file, data was usually missing or badly formatted.

None of it was anyone’s fault. The office manager exports whatever payroll’s year-end report gives them and sends it on. But every problem below means an email, a call, a corrected file, and days off a tight deadline. Here’s what goes wrong, and how the census portal example catches each one.

Missing data

• Hours for salaried staff. Payroll doesn’t track them, so the column is 0 or empty. But hours decide eligibility and vesting for everyone. The portal asks, and offers one click for 45 hours a week, which many plans use instead of counting.
• People who left. A termed employee drops off the active payroll report. Everyone employed at any time in the year belongs on the census. The portal compares with last year’s census and asks about anyone who’s gone.
• Birth dates. A lookup in someone’s spreadsheet that returned #N/A. The portal reads #N/A as empty and asks for it.
• A whole payroll file. After a mid-year switch, the January to March file from the old service is forgotten. The portal checks total deferrals against payroll’s year-end report; a gap points straight at what’s missing.

Formatting that Excel or payroll changed

• SSNs without their leading zero. Excel stores 078-05-1120 as the number 78051120. Put back automatically. (The same happens to New England ZIP codes.)
• Dates as numbers. 46113 is April 1, 2026 in Excel’s own counting. Turned into dates.
• Two-digit years. Is “2/28/58” 1958 or 2058? For a birth date it’s 1958; for a hire date “3/1/04” is 2004. Read by what the date is.
• Amounts as text. “$12,345.67” with a dollar sign and commas, “(120.00)” for a reversal. Turned into numbers.
• Names in one column, “HALVORSEN, MARGARET” in capitals. Split into last and first and written normally.

Data that’s there but wrong

• The latest hire date instead of the original. A rehired employee shows only the rehire date, which resets their service. The portal compares with last year’s census and asks: rehired? One click keeps the original date and records the rehire.
• Birth date and hire date swapped. Born 2025, hired 1992. Flagged, with a “swap” button.
• A masked SSN. XXX-XX-4417 is fine on a pay stub; the census needs the full number. Asked for.
• The payroll service’s test employee. “TEST, EMPLOYEE”, 999-99-9999. Flagged as not a real person, left out with one click.

Rows that aren’t people

Subtotals per tab, a grand total, the headings repeated at the top of every printed page, a “Confidential” footer. Read as people, the grand total row alone would be an employee with over a million dollars in wages. The portal recognizes and leaves them out, and lists what it left out.

Two files for one person

After a payroll switch, or when someone moved from hourly to salaried, the same person is on two or three rows. The portal combines rows with the same SSN and adds up pay, hours and deferrals, so the census has the year’s real totals.

What stays with the TPA

Some things are the TPA’s call, not the employer’s: who’s highly compensated, who’s a key employee, who went over the 2026 deferral limit ($24,500, plus catch-up from age 50), who might now be a long-term part-time employee under SECURE 2.0. The portal flags those for the TPA to confirm, instead of asking the client.

Key takeaways

• Most census problems are predictable: missing hours, missing people, and Excel’s formatting.
• Repair formatting automatically, but list every repair.
• Compare with last year’s census and with payroll’s own totals; that’s where missing people show up.
• Ask the employer only what they can answer, with the reason.

Try the census portal with a messy payroll file →

Run a TPA, or any business that collects data from its clients every year? We’ll build a portal like this for your clients, and because we work with AI-assisted development, it costs far less than you might expect. Let’s talk.