Easily Fix Dates Formatted as Text with Power Query
ฝัง
- เผยแพร่เมื่อ 2 ก.ค. 2024
- Power Query makes fixing dates entered as text in Excel super easy, and it's quick to update when you get new data.
Learn more about how Excel stores date and time in this comprehensive guide here: www.myonlinetraininghub.com/e...
Download the practice file: www.myonlinetraininghub.com/f...
Learn Power Query: www.myonlinetraininghub.com/e...
Excel 2010 & 2013 users download the free Power Query add-in here: www.microsoft.com/en-au/downl...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy - วิทยาศาสตร์และเทคโนโลยี
Mynda I'm brazilian and want to enormously THANK YOU for the lessons. Literally saved my job! God bless your job.
Wow, so pleased I could help!
@@MyOnlineTrainingHub 🙏🙏🙏🙏
Many thanks I had to use the locale method for the first time today really appreciate your channel Mynda!
Glad I could help 🙏
I cannot believe the timeliness of this video! I had this exact problem this week. Great help. Thank you for the video instruction. So simple!
Great to hear, George!
Brilliant lesson, Mynda, as always. Clear. Crisp. No waffling around (love your phrase). I am often faced with global database dates in... text format. Your lesson is cool, clever, and instantly actionable for me. I always learn more than I ever expect. Well done.
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 👍
Wow, Mynda, once again you read my mind and I got this video just in time, when I was struggling with dates in CSV! Thank you so much! 😊👍
:-D glad I could help, Vijay!
Hi Mynda. Awesome examples! Thanks for showing the various ways to use Power Query to fix dates formatted as text. Thumbs up!!
Thanks, Wayne!
Beautiful exposition! Also wanted to mention that I took your dashboard course and I use the techniques I learned there every day.
Awesome to hear!
I follow all your videos with great expectation, especially those about Power Query. Thank you very much for your dedication.
Glad you like them, Ivan!
Solved my problem instantly, thanks a lot!! Btw I love your accent.
Great to hear, Cris!
Thanks Mynda! dates are one of the most important data in data bases, these conversions are really usefull!!
Glad it was helpful, Felipe!
You are a life saver. Thank you so very much!
You are most welcome 😊
Cool steps, thanks mam. i dabbled with lot of options in Power Query before hitting your video. Loved your explanation and solved my problem.
Great to hear!
Thanks Mynda, I work with US dates all the time, and this really helps.
Glad it will be useful, Peter :-)
A talented expert I have ever seen before. A lots of respect from my heart ♥. God bless you.
Thanks for your kind words :-)
WOW !
I'm using Excel for years but this lesson was an eyeopener.
Thanks for this video (and all others on your channel)
Well done - you made my day 😀
Awesome to hear 🙏
Very detailed explanation.. Thank you very much...
My pleasure 😊
Mynda, thank you so much for saving me from trying everything that did not work.
Brilliant video as usual.
Glad I could help!
Thx for this lesson! You're really Beauty and a Beast (in Query) :)
😊 Glad it was helpful!
Thanks for showing us the versatile use of PQ. Great Video.
Cheers, Sunder :-)
Thanks Mynda. It's a really great content. God bless you!
Thank you, Alejandro!
Wow. Learnt two new things today: splitting by position and column from examples. How did I not know it was that easy? Thank you!!
Great to hear, Lindsey!
YOU ARE A GENIUS!
So pleased you found the video helpful 🙏😊
Saved me a ton of work! Thanks!
Great to hear!
Learned something new today, Thankyou
Glad to hear it!
I was looking for a video like that, thanks you because this is so useful
Glad it was helpful!
Great examples of date transformations!
Glad you like them, Doug!
@MyOnlineTrainngHub - Awesome stuff as ever Mynda. Thanks for sharing. Take care and stay safe!
Thanks! You too!
Hi Mynda - great tips and tricks
Thanks for watching, Heiko!
Amazing - Example 5 was my issue, super easy fix!! Thank you so much!!
Great to hear!
Thanks for sharing this Mynda, very useful tricks!
Glad you enjoyed it!
I didn't know that you could split columns by specifying the quantity as in your example (0.2,4). Very useful, good tips. Thank you very much Mynda!
Glad it was worth your time to watch, Luciano :-)
Super helpful! Very clear and useful!
Glad you think so!
Thank you so much for sharing this. It is super useful indeed.
Great to hear, Claire :-)
Hi Mynda! Thanks a lot for sharing your knowledge! It’s really helpful and I really like how you’re always using shortcuts =)
So pleased to hear that, Maria!
Hi Mynda!Really Great Helpful Tips.Being Able To Change Locale Is A Godsend...Thank You :)
Cheers, Darryl!
Fantastic lesson and presentation. Thank you so much! (I can't imagine what the four trolls with the thumbs down as I write this were thinking!)
Thanks so much, Dallman!
Where has this been all week? I have been stuck trying to solve the issue with mixed date. You definately have a new sub. Thanks sooooooo much
Awesome to hear, Mike!
Great Madam, This tutorial is simply superb, You have explained in a very simple way to understand. Thanks again. Looking forward to many more on PQE.
Thanks a lot 😊
Brilliant! Solved my issue. Many thanks 🙏
Great to hear!
This helped me today. Thank you
I'm so glad!
Hi Mynda. Awesome video. Especially the last example and how to use the locale option. Keep it up.
Glad it was helpful, Rohit!
I'm always waiting for ur great content like this
Glad you enjoy it!
Thank you so much. This is very helpful.
You're very welcome!
Thanks Mynda. That was very helpful!! :) :)
Pleased to hear that, John :-)
Many thanks. It's really a great lesson.
Glad you liked it!
Thank you very much, this helped me a lot.
Glad to hear that!
Incredibly useful 👌
Great to hear!
excellent lesson. thank you!
My pleasure 😊
Este es el mejor video para transformar fechas en formato texto. Gracias por compartir conocimiento
Glad you found it helpful!
thank you sharing, this one was very helpful
Great to hear!
Very helpful, thank you.
My pleasure, Keith!
Thank you so much, that was amazing 😄
You're welcome, Gilberto! 😊
Just awesome ❤
Thanks a lot 😊
Hello, Really amazing! this video really helped me fix my date problem in power BI. Thank you so much!
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/
Solved so many issues. Thanks.
Cheers, Chris!
Thanks! Very useful 😄
Glad to hear that, Ian!
You are amazing!! Thank you so much
Glad I could help :-)
Thanks Mynda. PQ rules!
It so does, Chris :-D
Brilliant video thank you
Glad you enjoyed it 😊
Very useful for those who work with system generated reports since those exported report have non compatible date formats with excel.
Glad you liked it, Nipun :-)
Great tutorial! Thanks :-)
You're welcome!
Many thanks i have date issues in large files and this will be very helpful
Pleased to hear that, Clive!
Thank you this video is helpful 😊
I'm so glad!
Thanks Mynda 👍🏻
My pleasure, Graham :-)
Thank you so much for this wonderful lesson. I wonder if I can use the power query with the Mac version of Excel?
Thank you so much!
You're welcome!
Thank you for the video! Helped me a lot as I kept getting errors whenever I tried to swap to a whole number.
Great to hear!
Wow, this has really helped me
Awesome to hear 😊
Its really great .thanks .
You are most welcome :-)
Mil gracias por tu ayuda !
My pleasure 😊
I work with us date format. This is super easy formatting tips. Generally i use text function to change date day and month but i have to do it every time from us to india or india to us format.
Thanks a lot mam
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.
very useful thanks a lot
You are most welcome, Mohamad!
Is realy need .......Date is big matter madam ...... U r excel The Boss
Glad you liked it 😊
Thanks. I always felt like col from example was cheating but I'll use it as a quick fix for date formatting!
Glad it'll be helpful!
I didn't know that is very helpful
Glad to hear that, Mohamed :-)
Thank you so much Mynda! I've been struggling with the last form of date conversion every month for years now and never thought to try PQ... it works flawlessly!
So pleased to hear this video was helpful :-)
Thank you
You're welcome :-)
Спасибо, солнышко!
My pleasure :-)
Awesome ❤️❤️❤️
Thanks 🤗
Excellent
Cheers, Perry!
Thanks Mynda - i get dates from my US colleagues and I always have a struggle when I try to integrate with my dates here in Ireland.
Glad it was helpful, Seamus :-)
=Rept("👍",1000) -- "Excel"lent video - this is such a common problem, people copying data into excel from different countries and the dates are a total mess, and there are sumifs() function using the dates to get say monthly totals. And of course, everything is wrong. I really like the different concepts of getting the date correct. going to link this video to many people that have date issues in their files.
So pleased it was helpful, and thanks for sharing the video!
I download csv file from my bank and this one bank's date always causes an issue which after watching your clear and concise video, still has yet to solve the issue. I finally just typed out the dates to get it to work (time consuming from all angles). I would love the silver bullet on these dates.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I want to learn more about the dax formula that you've done in dashboard post. Could you please send me a relevant course, please? I might learn it over the Christmas break.😁
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
The best
Thank you! 😊
Hi Mynda, quick question, your explanation is fine if you have 1 column of date formatting issues but i have multiple date columns per tab and 6 tabs. Splitting each column across 6 tabs would take me ages !! How do i re-apply the fix across multiple columns/tabs/sheets?
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
Thank you for the extremely useful video. When you did the change date to locale at 12:00, you set it as the US. but your Excel default is Australian. So if your default was something else, (say another country that uses American style, It would just load it as American style. Is that correct?
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 Mynda, great video! I have a question: how can I calculate the time (duration in hh:mm:ss) in Power Query / Power BI? For example, I need to add working hours for different projects. In my Excel file, I have the duration for each activity. When I work with my table in Power Query or Power BI, the format change into a date-time format and I can't do calculations with that.
Thanks, Dan! To answer your question, you can change the data type to Time in Power Query.
@@MyOnlineTrainingHub thank you Mynda
I found this lesson extremely helpful. However, I have 2 quick questions: in the custom formating option, why the month and year have more letters than day like this: d/mm/yyyy. My 2nd question is that I notice there is a date/time/timezone option in the power query but don't know why you don't select it instead.
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
Thanks Mynda that's very helpful. I have file coming to me from Project Managers all over the world and so from mutiple locales. They are all text fields. Is there a way to process data from multiple locales into the format in my locale. Concretely, I have text fields from UK in 01122020 format and12012020 format. Thanks
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. :)
Thanks for the lesson - learned a lot.
I have a Question about the Import.
My csv Data shows the Date like 02.01.2021 (EU 02.Jan, 2021) when i load in my files to Power Query it shows me a text 2012021 - other Dates like 30.01.2021 will show up as 30012021 (1 more Number)
With both i have wrong data with Dates in future or an "Error" is showing up - is there any fix to that Problem? - or is there a way to tell power query that he just have to import the data without removing something like the Dots or the Zerro in front?
Thanks a lot
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
Hello, I have a data source where the date is written as 1st, 2nd, 3rd... of each month and year. The Date.FromText function returns an error. Is there an easy way to convert a string such as '8th November 2023' to a date?
I often watch your Excel videos and find them very informative, so thank you for the very useful guides which you produce.
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.
I have a question. What happens when you have a row of promoted date headers that need to be changed from text? Right now, I just convert them in excel to become the right data type so that I can use Index/Match/Match to make a report. Wondering how that one row gets converted into the right date format so I can avoid the extra step in Excel.
Ideally, you should never use dates as headers. Sounds like your data needs unpivoting into a tabular layout: th-cam.com/video/-IMqkg35adA/w-d-xo.html
This was so beautiful because I was stuck in trying to change January to Jan and February to Feb but when you said it is done in excel and not in Power Query. I thought OMG now I know why I got stuck. Didn't know that you can't change full month names to shorter names in PQ. Why did you put 0,2,4 for Positions? This is confusing. In my example, some are 11/29/2019 and some are 1/7/2018 so what would be positioned for these examples? Thanks
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.
Hi Lynda,
What I want to learn is that since there are Microsoft engineers who developed the DAX software language and have a much broader formula and data analysis power with the use of DAX, why do the formulas we use in excel have such limited capacity?
More precisely, when there was a powerful analysis program like Excel, why was the need to develop a program like PowerQuery and use a different software language?
Thank you
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.
Mynda, I've come across dates where its shown as August 1st or July 2nd etc. I've been using Replace to remove st, nd, rd, and th from the dates and then a simple date format change is done, but I'm wondering if you have a simpler way of formatting this?
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?
Hello, Mynda. Just wondering what would be the solution to dates formatted as 2/6/2018, 6/2/2018, 06/02/2018, 02/06/2018, 6/2/2018, 2/6/2018, 6/02/2018 into one date format as there have been different format texts from a file in the past I'm keen to convert into DD-MM-YYYY? Length of texts is 6,7,8,10 digits if I replace /. For example: Some of them formatted as the month in very left side while other months are positioned in the middle.
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
i have a column that contains data like jan, feb, mar but this column not converting into date format in power bi power query
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I know how to = TEXT([cell with date], “ddd”) is this what the video is about? I recently learned about this formatting! Came here to see if this was about that, but it premiers in 36 hours. I’ll check back later!
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.
Stuck on the first example ma'am. It keeps returning "Error" for the merged column after trying to change the data type from number to date. I'm confused.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum