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

[u/Nyx_Night]

Hey there, I'm fairly new to excel (Beginner) and eager to learn.

Microsoft 365 MSO (Version 2608 Build 16.0.20326.20072) 64 Bit (Language: German)

The Problem:

I'm designing a permit register in Excel and I'm trying to find the most future-proof way to handle dates with different levels of precision.

My date columns may contain:

• Exact date: 15.04.2027
• Month and year only: 04.2027
• Year only: 2027

The challenge is that these values represent different levels of certainty. If I convert everything into a true Excel date, I would need placeholders such as:

15.04.2027 -> 15.04.2027

04.2027 -> 01.04.2027

2027 -> 01.01.2027

Current Thinking:

Dates stored als text that the level of certainty remains visible to users.

Downside would be filtering, sorting, date calculations and future reporting may become mor difficult?

Long term, I'd like to use Power BI and build reports /deadline tracking and so on.

What would be considered a best practice in this situation?

• Store everything as text and parse later in Power Query/Power BI?
• Store everything as actual dates using placeholder values?
• Use separate date and precision fields (even though I'd prefer to avoid additional columns)?
• Any other common approach used in project management, permitting, regulatory tracking, or PMO environments?

I'm interested in solutions that scale well and remain maintainable when the workbook is handed over to other users in the future.

Thanks for your help :)

Edit: Thanks for your Impressions and Sharing of Knowledge :)