Handling mixed date precision (DD.MM.YYYY, MM.YYYY, YYYY) in Excel for filtering and future Power BI reporting

[u/Nyx_Night]

/r/excel/comments/1w54ne5/handling_mixed_date_precision_ddmmyyyy_mmyyyy/

[u/Bright_Mix_773]

Separate precision column, and do not skip it because it is an extra column - it is the only one of your three options that is not lossy.

We keep a register of 64,938 regulatory filings with exactly this shape: a date that is sometimes exact and sometimes inferred. Three things it taught us.

Store the interval START as a real date and the precision as its own column. "2027" becomes 01.01.2027 plus precision=year. Sorting, filtering and Power BI date tables all work off the real date, and nothing has to be guessed at read time. The placeholder-only version you sketched cannot be undone: once 2027 is written as 01.01.2027 with nothing recording that it was a year, it is indistinguishable from a permit genuinely dated 1 January, and no parsing later gets it back. Text-only has the mirror problem - the certainty stays visible to humans and vanishes for every calculation.

For deadline tracking, store or derive the interval END as well. A year-precision deadline of "2027" means 31.12.2027, not 01.01.2027. Keep only the start and every year-precision row shows overdue for twelve months and every month-precision row for up to thirty days. That is the bug that made us add the second column, not any theoretical worry.

The reason the precision column earns its keep is that it catches bad source data, not just fuzzy source data. In our file 3,569 of the 64,938 dates (5.5%) come from a fallback rule rather than the primary field, and they carry a flag saying so. Following that flag turned up something we would never have looked for: 554 of the 808 entities have at least one filing where the date printed on the document is more than three days before the document was actually filed, one of them in 80 of its 86 filings, and the largest single gap is 3,287 days - a cover page declaring an event nine years before it was submitted. A source putting a date in a box is not the same as that date being right. Without a column recording where each date came from, all of that is invisible and silently wrong, and it will land in your Power BI deadline report as fact.

Practical shape that survives handover: one date column (interval start, real date type), one end column, one precision column with a fixed vocabulary (day / month / year), and one source column. Four columns, no parsing rules living in anyone's head.