Forum Discussion
Solved
Reformatting and change the order of date
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
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!
4 Replies
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.
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..
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!
