Reformatting and change the order of date

[LarissaM711]

Hello,

i recieved a excel that used to be a csv, so this export does not give me a date format that I can use for my reporting AND the file contain dates in different formats. I see some of them are text, others are dates but I can't change them all to dateformat.

also, I wanted to set a formula in another column that formats the date in column B correctly. However, my problem is that I want to change the order from month/day/year to year/month/day, so I don't know how to do that.

i tested something like:
=TEXT(DATE(YEAR(B2),MONTH(B2),DAY(B2)), "MM.DD.YYYY") but i get Errors

I created a Example Excel

[#2]

First of all I would suggest importing your csv into Excel via Power Query - opening a csv directly in Excel will try and automatically convert any text dates into actual date fields which can not have a desired outcome.

I think your issue is that 07/14/2023 is being treated as text which is likely due to yur regional settings.  If you're operating on a desktop, this needs to be done at the PC level (search regional settings in the start menu). If you're on Excel for web, click File > Options > Regional Format Settings and make sure this matches what you're expecting (Excel for web).

A workaround is to change the csv to a txt which will prevent Excel from automatically trying to parse the dates - you can manually split the file then.

[#3]

@BA_Max 

Hey,

i got the csv now and tried to convert the text Date-field with PowerQuery in a Date-field. But here it is the exact same strange thing, that some Rows cannot tbe transformed into a date-field. (Error: The specification for a DateTime value could not be analysed)

I don't know what to do... I attach the CSV here..

[#4 · Accepted solution]

@LarissaM711 as this is a Locale issue, you need to process the date/time as a US type date (that's what it looks like).

Try adding this step in after the initial loading of the data into Power Query:

= Table.TransformColumnTypes(#"Changed Type", {{"Veröffentlichungszeit", type datetime}}, "en-US")

This can be done through the editor by clicking the following:

(using Locale)

Date/Time & English (US)

Hope that helps!

[#5]

omg yeay! Worked!
Thank you so much :)