Saw a pile of blank dates come through as 12/30/1899 in a CSV export from Access, which is just the day zero placeholder — picture a ruler that starts at zero so blanks still land on a tick. Do you explain that to stakeholders, or just convert it and move on?
I’d fix it at the source: in the Access export query, use IIf(IsNull([YourDate]), “”, Format([YourDate], “yyyy-mm-dd”)) so blanks don’t turn into 12/30/1899 — Excel’s epoch’s weird uncle. Only caveat: if 1899-12–30 could be a real value, don’t just “convert it and move on”; map nulls to empty string/NULL and document it. Do you control the export query, or is this coming from a canned report?
Convert it and log it. In an Access CSV export, I map 12/30/1899 (the “day zero”) to blank/null and record the count, then add a one‑liner in the data dictionary so stakeholders know it’s an epoch quirk. @labcompass74’s formatting trick is good; I just add the count check so a sudden spike flags real missing data.
I usually fix it upstream in Access: set the date field’s Default Value to Null (not 0) and strip out Nz()/CDate on blanks so the CSV doesn’t invent that December 1899 phantom. , it trips people, so I add a one‑liner with your “ruler that starts at zero” and normalize downstream with a small tally of replacements; if anyone pushes back, I send them this: Content retirement | Microsoft Learn. If you can’t touch the Access file, do you prefer correcting it in Power Query or in your loader?
In Power Query, swap 0 to null before Date type — your ‘ruler that starts at zero’ line works.
I treat that sentinel like “000–00-0000”: a rule auto-nulls any date <= 1900-01–01, logs the count, and surfaces it on a QA card so stakeholders see a metric, not a mystery. Agree with @hlopez85 on fixing the source; if you have 19th‑century dates, keep a whitelist. Do you have any real pre‑1900 records?
Quick gotcha I’ve run into: if 12/30/1899 shows up with a non‑midnight time, it’s really a “time-only” value, so I strip the date during ingest and keep the time; if it’s exactly midnight, I cast it to null. Do you have any columns where times were stored without dates? If there’s even a chance of legit 1800s dates, I whitelist those sources before applying the rule so I don’t erase real history.