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 :)

[u/AutoModerator]

/u/Nyx_Night - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ProspectiveWhale]

You could have Date, Month and Year be different columns.

The precision would then be implicit when a column is blank.

Not sure why you need the 3 types, but mixing up data like that generally makes it hard to manage.

But also, is there a reason you need imprecise dates?

I'd imagine for a permit register, you'd want exact dates. Even if it's displayed as Month/Year only, the system should be clear on what date that means rather than it could be any day of the month/year.

[u/Nyx_Night → u/ProspectiveWhale]

It's a Permit Register, sometimes there are no precise Dates of Approval/ Submission / Executions, but the people in my company need to track the estimates until there is a precise Date.

[u/ProspectiveWhale → u/Nyx_Night]

So it may not have been approved yet, but you need an estimate.

In this case, what I'd want personally, is to keep separate actual data and estimate data.

Not just separate the Date, Month Column, but have actual separate columns for final dates and estimate dates.

So that it's clear that's an estimate.

I would imagine there are some things that an estimate should not be used for.

So this leaves the column blank and ensures when you pull data from actualDate, it won't accidentally treat an estimate as real data.

When you have something where an estimate is taken when actualDate hasn't been set, use a conditional to pull from estimateDate when actualDate is empty.

Something like that.

So you have to purposely pull from the estimate column and they're excluded by default, rather than building contingencies to exclude estimates when they're not supposed to be used.

[u/BackgroundCold5307]

Never a good idea to mix different kind of data , (i.e. year only, month/date etc). Suggest different col for different part of the date would be best. Easy to solve and slice and dice at a later time too

[u/Nyx_Night → u/BackgroundCold5307]

Ty :)

[u/clarity_scarcity]

You say you’re willing to learn so spending 60 seconds to lookup how dates work in Excel would be a good investment. Hint: dates are actually handled as a numbered timeline in Excel, with 1 = 1.1.1900 making today 46267. Non-numeric values are never dates in Excel, and don’t get confused by visual formatted anything that is pretending to be a date but actually isn’t in Excel terms. One quick test against potential imposters is =ISNUMBER(). No number? No date.

[u/Nyx_Night → u/clarity_scarcity]

Ty for your Input and a good hint with how dates work. It's a lot to start with and my way of learning best ist asking from time to time to get a headstart with things. Now I have a lot to look up :)

[u/lolcrunchy]

Force every date to be an exact date. If they are deadlines, then they are the last day of the period i.e. 2027 -> Dec 31 2027.

In Power BI, you will probably have an autodate table which will handle grouping or filtering by different calendar granularities. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

[u/incendiary_bandit]

My first response was oh god don't mix. Date is date but someone else might have other opinions.

Why do you have different levels of precision? Seems like you almost need a start and finish. You could also have a field to define the input type of day, month or year and then the input is always the full date.

I had users previously entering things mid 2027 or sometime next year in a big risk tracking spreadsheet. It's amazing how many different ways users can break shared spreadsheets.

[u/fastauntie → u/incendiary_bandit]

People can only work with the data they have, and in some circumstances complete dates simply aren't available. I don't know OP's application, but I've had a number of situations when I did not have complete dates to use. It's just not helpful to insist that people need to generate data that doesn't exist.

[u/Nyx_Night → u/incendiary_bandit]

Thanks for your Response :) The company I work for needs to do a lot of land acquisition in GERMANY (lot of paperwork and slow public offices to rely on). So sometimes there is no exact Date to use, just a month when the approval happens (for example)

[u/conduit_for_nonsense]

Firstly, you work to establish the same precision for the input data, so you always recieve it in DD/MM/YYY.

If 2026 is that same as 01/01/2026, then you can do the replacement in your data.

If 01/01/2026 is only an estimation, you can add a second column with the placeholder date for reports and keep the first text column as the true date.

[u/HandbagHawker]

convert everything to dates.

[u/AutoModerator → u/[deleted]]

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/fastauntie]

People who are insisting that you make every date an exact date are thinking about the frustrating reality that Excel doesn't handle incomplete dates well, but they're prioritizing that over the fact that in many cases incomplete dates are all you have. This means that you have to think around Excel, not falsify your data to satisfy it.

The way to do this to keep separate columns with numbers for the year, month, and day. This makes it easy to sort and filter and to create formulas to do any calculations you need, and those formulas can update results automatically when you add more precision.

If you need to display exact dates somewhere you can have another column that derives them from the first three where they're all complete and has blanks where they're not. The presence or absence of data in the month and day may be enough for you to see the level of precision, but you can also use that to build another column to indicate precision in any way that's most helpful for you.

One key to working with computers is that often you have to figure out how to work around their limitations to tell them how to use the data they have in ways that are helpful to you, rather than the ways that they've been programmed based on someone else's ideas of what is usually helpful.

[u/Nyx_Night → u/fastauntie]

Thank you, for your help:)

[u/christiansteven]

I would keep the precision explicitly rather than turning unknown dates into fake exact dates.

For example, use:

• Year: 2027
• Month: 4 or blank
• Day: 15 or blank
• Precision: Day / Month / Year

Then create a separate calculated date only when you need one for sorting, filtering or Power BI. If you use a placeholder such as 01.04.2027, keep it separate from the original values so nobody mistakes it for the actual date.

This will also translate cleanly into Power BI later. You can build a proper date for reporting while still knowing whether the source actually said "15 April 2027", "April 2027" or simply "2027".

I wouldn't store everything as text in one column. It looks simpler now, but makes sorting, filtering and reporting harder later.