Easily Fix Dates Formatted as Text with Power Query 

Wow, so pleased I could help!

@@MyOnlineTrainingHub 🙏🙏🙏🙏

Thanks for your kind words, Libby! So pleased you can make use of it :-)

Honestly great teaching methodology. Facing dates issues from SAP Data in excel. Great 👍

Great to hear, George!

:-D glad I could help, Vijay!

Thanks, Wayne!

Awesome to hear!

Glad you like them, Ivan!

Glad it will be useful, Peter :-)

Glad it was helpful, Felipe!

Thanks for your kind words :-)

Awesome to hear 🙏

Glad I could help!

Cheers, Sunder :-)

Thank you, Alejandro!

You are most welcome 😊

Great to hear!

Great to hear!

Great to hear, Lindsey!

Glad you like them, Doug!

My pleasure 😊

Glad it was worth your time to watch, Luciano :-)

Glad you enjoyed it!

So pleased you found the video helpful 🙏😊

Glad I could help 🙏

So pleased to hear that, Maria!

Thanks! You too!

Glad to hear it!

Cheers, Darryl!

Great to hear, Cris!

Glad you think so!

Great to hear, Claire :-)

Great to hear!

I'm so glad!

You're very welcome!

Thanks for watching, Heiko!

Thanks so much, Dallman!

Glad it was helpful, Rohit!

Great to hear!

Glad it was helpful!

Glad you liked it!

Thanks a lot 😊

My pleasure, Keith!

Glad to hear that!

Awesome to hear, Mike!

My pleasure 😊

Cheers, Chris!

Glad to hear that, Ian!

Great to hear!

Glad I could help :-)

Glad you enjoyed it 😊

😊 Glad it was helpful!

Glad you found it helpful!

I'm so glad!

Pleased to hear that, John :-)

Great to hear!

Glad you enjoy it!

You're welcome!

You're welcome, Gilberto! 😊

Pleased to hear that, Clive!

You are most welcome :-)

You're welcome!

You are most welcome, Mohamad!

My pleasure 😊

Awesome to hear 😊

Glad you liked it, Nipun :-)

It so does, Chris :-D

Great to hear!

My pleasure, Graham :-)

Great to hear!

@@MyOnlineTrainingHub Hi, What are your courses listed on UDEMY? Thank you!

Hi Mamadou, thanks for your interest in my courses. My Udemy courses are out of date. I'm not using that site anymore, but you can get all my courses from my own site here: www.myonlinetraininghub.com/

Thanks a lot 😊

So pleased to hear this video was helpful :-)

Glad you found it helpful, Deependra. I would avoid using the TEXT function because then the dates are not usable in formulas or PivotTables etc.

You're welcome :-)

Glad to hear that, Mohamed :-)

Glad you liked it 😊

Glad it'll be helpful!

Cheers, Perry!

Thanks 🤗

My pleasure :-)

Thank you! 😊

Thanks, Dan! To answer your question, you can change the data type to Time in Power Query.

@@MyOnlineTrainingHub thank you Mynda

No. The locale you choose in the dialog box is the locale of the source data. Power Query will then convert it to the locale that your PC is set to.

Hi Daniel, it really depends on how the date is formatted, but either way you'd have to apply the fix to each tab as a separate query unless you're consolidating the data into one table. It's difficult to help here, but if you post your question and sample Excel file on our forum someone can help you further: www.myonlinetraininghub.com/excel-forum

Hi Christian, You can use Text.PadStart to add a zero to the dates 1st to 9th of each month. Then you can split the result into the 3 date components (day, month and year) and rebuild the date using the #date function. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

should read " and from US in 12012020 format"

Great to hear it'll be helpful, Seamus! For the different locale files you'd need to change the type using locale on each individual file before appending them into one consolidated table.

@@MyOnlineTrainingHub Thanks Mynda - i was afraid of that. :)

Glad it was helpful, Seamus :-)

Glad you found my video helpful, Toan :-) To answer your questions: 1. d/mm/yyyy will format dates with a single digit day with one character e.g. 1/10/2020 whereas dd/mm/yyyy will format it 01/10/2020 2. Date/Time/Timezone format requires the timezone to be part of the value e.g. how many hours from UTC the time is. More on date timezones in Power Query here: docs.microsoft.com/en-us/powerquery-m/datetimezone-from

Great to hear you want to learn DAX. You can do so in my Power Pivot course: www.myonlinetraininghub.com/excel-dashboard-course

@@MyOnlineTrainingHub - Thank you

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

You could probably use Split to separate the last 2 characters in one go.

@@MyOnlineTrainingHub I should have said my dates show as January 4th, 2021. At the moment I find if I remove the nd/rd/th with replace it works fine but with "st" because of August ending with "st" I need to use a replace of "st," to save the spelling of August. Any other suggestions?

So pleased it was helpful, and thanks for sharing the video!

Hi Bilguun, you can't do this in Power Query because all dates in the column aren't the same format i.e. either text or number. Instead, in Excel add a column that determines whether the dates are text or not using the TEXT function. Then sort the column based on the result. You can then isolate the text values and fix them using one of the options suggested here: www.myonlinetraininghub.com/fixing-excel-dates-formatted-text

@@MyOnlineTrainingHub Thanks, Mynda. What I did was first I separated them using text to columns, then using a formula that will merge them together for example, if F,G,H columns contains either MM,DD,YYYY or DD,MM,YYYY, used =F2&"/"&G2&"/"&H2 formula to join them, then using =date value, lastly format cells so I can convert them to my desired format which is DD/MM/YYYY, but came back as multiple errors as a few of them formatted in 10 digits like 1/2/2020 which could be 01/Feb/2020 or 02/Jan/2020. Pretty long process, isn't it? If I could use just one formula which will isolate them while putting them to only one format would be fantastic... Not sure if this could be possible but what about Using IFERROR, IF formulas to put these bad guys into one column or even with VLOOKUP just for the sake of it would do the magic and if anyone knows how to, your assistance on this matter will be invaluable. Cheers

Ideally, you should never use dates as headers. Sounds like your data needs unpivoting into a tabular layout: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--IMqkg35adA.html

Hi Emre! DAX is designed to be used on relational data, whereas Excel functions are not. This new capacity with Power Pivot required a new language that's design is modeled on the familiar Excel functions. Keep in mind that Excel is built on millions of lines of 35+ year old code. It's not so easy to just change, but we're seeing massive improvements to Excel functions e.g. cells containing rich data types, dynamic arrays, LET and LAMBDA to name a few and there's more coming. Power query is a different tool, it's all about getting and transforming data and that requires a more advanced coding language than Excel functions can give us. The Excel team did a great job of automating the writing of this code by giving us a nice GUI that allows us to click some buttons and have the code written for us.

Hi Nigel, you could split the number from the 'nd' 'rd' etc. text or replace these text strings with nothing to isolate the number, then it should convert to a date more easily. If you're stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@@MyOnlineTrainingHub .. Thank you.. yes, I did a bit more investigating and splitting the text into constituent parts does seem to be the only method, then applying several steps to remove the ordinal suffixes, then recombine into a date string which Power Query can handle. It does seem like a curious oversight.

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

No, this video is about fixing dates that have been entered/imported as text and you want to fix them so they're proper date serial numbers that work in formulas, PivotTables etc. I don't recommend using TEXT to format dates unless it's for a chart label or other label.

@@MyOnlineTrainingHub ah I see, cool, I’ll check it out later. Thanks for the tip. I’ve got an invoice date on the table, but wasn’t able to sort according to weekday or week in a pivot table, so I added a weekday and week column column in a query first and ran the pivot table off the query. I didn’t want to add those to my original table set.

Yes, but you'll have to add a value for the day e.g. the 1st of each month.

Glad you found it useful! The 0,2,4 example only applies to dates in this format: 18072020 or 01122020 etc. as in the example shown i.e. where there are no delimiters and the structure is ddmmyyyy or mmddyyyy. If you have delimiters, like the forward slash, then you would use one of the other techniques I showed.

@@MyOnlineTrainingHub OK! Thank You!

@@MyOnlineTrainingHub You know what? I was able to get shorter months from Power Query. Someone told me that I will need to manipulate M Code and that is what I did and I was able to get Shorter months from PowerQuery. Thought of letting you know that you can do it in PowerQuery. Thanks

Yes, you can, but those months are now text and therefore not usable in formulas, nor will they sort properly because Excel cannot understand them as calendar periods. This is why formatting like this is best done in Excel using cell formatting on date values.