Date Format Error When Importing Data Into Excel

Question

I am trying to import some data into Excel from a text file, however I get the following error in regards to the Date column:

"[DataFormat.Error] We couldn't parse the input provided as a Date value."

The dates are written in dd.mm.yyyy format in the original text file's data, however only some of them seem to convert correctly. I need all of the dates to convert correctly as I need to split the data into six month periods as the file is over Excel's row limit. What can I do to import all the dates correctly?

Answer 1

Select that column.

Data>Text to column>Next, Next> Choose date, Choose DMY>Finish.

It will convert it to date value, then you can set any date format on it.

Result:

Answer 2

Hi, thank you for your response, however the issue is occurring during the data import process and thus I am not yet able to access the Text To Column feature until I have finished importing the data, which I can not do as there are too many rows hence why I need to split it by date. Is there a way to adjust the formatting of the date in Power Query while importing the data?

Answer 3

1. Split by "."

2. Change column type to text

3.Combine them into custom column

3. set it to date format

4. Remove other column and keep the date column only.

Answer 4

When you change the data type, go via 'Use Locale...'. Then select Date and a Language (Country) that uses the dd.mm.yyyy notation. For instance, German (Germany) is one, but there may be more.

Now, PQ will automatically transform the 'text-dates' to your real dates in system's default format.