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.
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
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!
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/
As a Canadian who's lived in the UK and US, I've run into this issue for decades, esp the last 2, after incorporating one of our cos in the US. So I've got bank records with both, often intermingling in the same database (with a USD to CAD conversion column), and have used a variety of workarounds. Last year I watched your wonderful channel and starting using Power Query. Thanks for that! However, today I went to transform some dates listed 'dd/mm/yyyy' by using the LOCALE method, and after selecting DATE I chose 'English (Canada)'. The result was not what I expected (as here in Canada we've grown up with day month year): all of those dates in the 'mm' position were deleted (I forget what PQ called them) leaving me with only the dates that had month values at 12 or less. Working on a theory that Canada may have finally been subsumed into USA accounting formats I tried selecting 'English (Australia)' instead: AND IT WORKED! We need some sort of international agreement: For the last two decades I've been converting ALL dates to DD-MMM-YYYY [if that's how you get 21-MAY-2022] > I realize it's just so we can deal with the yanks, but what else to do?
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.
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
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.😁
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.
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 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.
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
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.
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?
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
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.
=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.
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?
@@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?
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.
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
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.
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
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.
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 🙏🙏🙏🙏
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 👍
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!
Thanks Mynda! dates are one of the most important data in data bases, these conversions are really usefull!!
Glad it was helpful, Felipe!
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!
Mynda, thank you so much for saving me from trying everything that did not work.
Brilliant video as usual.
Glad I could help!
A talented expert I have ever seen before. A lots of respect from my heart ♥. God bless you.
Thanks for your kind words :-)
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!
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!
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 😊
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.
Hi Mynda. Awesome examples! Thanks for showing the various ways to use Power Query to fix dates formatted as text. Thumbs up!!
Thanks, Wayne!
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!
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 🙏
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!
Many thanks I had to use the locale method for the first time today really appreciate your channel Mynda!
Glad I could help 🙏
Thanks Mynda, I work with US dates all the time, and this really helps.
Glad it will be useful, Peter :-)
Amazing - Example 5 was my issue, super easy fix!! Thank you so much!!
Great to hear!
Thanks for showing us the versatile use of PQ. Great Video.
Cheers, Sunder :-)
Just awesome ❤
Thanks a lot 😊
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!
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 :-)
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 :-)
Brilliant video. Thank you so much. A great time saver.
Glad it helped!
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 :-)
Very detailed explanation.. Thank you very much...
My pleasure 😊
Is realy need .......Date is big matter madam ...... U r excel The Boss
Glad you liked it 😊
Thank you so much for this wonderful lesson. I wonder if I can use the power query with the Mac version of Excel?
Great examples of date transformations!
Glad you like them, Doug!
Many thanks i have date issues in large files and this will be very helpful
Pleased to hear that, Clive!
Hi Mynda. Awesome video. Especially the last example and how to use the locale option. Keep it up.
Glad it was helpful, Rohit!
Solved my problem instantly, thanks a lot!! Btw I love your accent.
Great to hear, Cris!
Learned something new today, Thankyou
Glad to hear it!
You are a life saver. Thank you so very much!
You are most welcome 😊
Este es el mejor video para transformar fechas en formato texto. Gracias por compartir conocimiento
Glad you found it helpful!
Hi Mynda - great tips and tricks
Thanks for watching, Heiko!
Saved me a ton of work! Thanks!
Great to hear!
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!
I was looking for a video like that, thanks you because this is so useful
Glad it was helpful!
Brilliant, Thanks Mynda 👍👍❤❤
Glad it was helpful!
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/
Hi Mynda!Really Great Helpful Tips.Being Able To Change Locale Is A Godsend...Thank You :)
Cheers, Darryl!
Solved so many issues. Thanks.
Cheers, Chris!
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!
Brilliant! Solved my issue. Many thanks 🙏
Great to hear!
As a Canadian who's lived in the UK and US, I've run into this issue for decades, esp the last 2, after incorporating one of our cos in the US. So I've got bank records with both, often intermingling in the same database (with a USD to CAD conversion column), and have used a variety of workarounds.
Last year I watched your wonderful channel and starting using Power Query. Thanks for that!
However, today I went to transform some dates listed 'dd/mm/yyyy' by using the LOCALE method, and after selecting DATE I chose 'English (Canada)'. The result was not what I expected (as here in Canada we've grown up with day month year): all of those dates in the 'mm' position were deleted (I forget what PQ called them) leaving me with only the dates that had month values at 12 or less. Working on a theory that Canada may have finally been subsumed into USA accounting formats I tried selecting 'English (Australia)' instead: AND IT WORKED!
We need some sort of international agreement: For the last two decades I've been converting ALL dates to DD-MMM-YYYY [if that's how you get 21-MAY-2022] > I realize it's just so we can deal with the yanks, but what else to do?
Glad my video was helpful! I don't envy your date experience.
This helped me today. Thank you
I'm so glad!
Thanks Mynda. It's a really great content. God bless you!
Thank you, Alejandro!
Thanks for sharing this Mynda, very useful tricks!
Glad you enjoyed it!
YOU ARE A GENIUS!
So pleased you found the video helpful 🙏😊
Thank you so much. This is very helpful.
You're very welcome!
Thank you very much, this helped me a lot.
Glad to hear that!
Super helpful! Very clear and useful!
Glad you think so!
Thx for this lesson! You're really Beauty and a Beast (in Query) :)
😊 Glad it was helpful!
Many thanks. It's really a great lesson.
Glad you liked it!
thank you sharing, this one was very helpful
Great to hear!
@MyOnlineTrainngHub - Awesome stuff as ever Mynda. Thanks for sharing. Take care and stay safe!
Thanks! You too!
Very helpful, thank you.
My pleasure, Keith!
Brilliant video thank you
Glad you enjoyed it 😊
Incredibly useful 👌
Great to hear!
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
Thanks! Very useful 😄
Glad to hear that, Ian!
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!
Thank you this video is helpful 😊
I'm so glad!
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 :-)
Thank you so much!
You're welcome!
You are amazing!! Thank you so much
Glad I could help :-)
Thank you
You're welcome :-)
Wow, this has really helped me
Awesome to hear 😊
Mil gracias por tu ayuda !
My pleasure 😊
Thank you so much for sharing this. It is super useful indeed.
Great to hear, Claire :-)
Thanks Mynda 👍🏻
My pleasure, Graham :-)
Thanks Mynda. That was very helpful!! :) :)
Pleased to hear that, John :-)
very useful thanks a lot
You are most welcome, Mohamad!
Thanks Mynda. PQ rules!
It so does, Chris :-D
Its really great .thanks .
You are most welcome :-)
Great tutorial! Thanks :-)
You're welcome!
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
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
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 didn't know that is very helpful
Glad to hear that, Mohamed :-)
I'm always waiting for ur great content like this
Glad you enjoy it!
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.
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. :)
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.
Thank you so much, that was amazing 😄
You're welcome, Gilberto! 😊
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
Excellent
Cheers, Perry!
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.
=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!
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?
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: ruclips.net/video/-IMqkg35adA/видео.html
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
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
Спасибо, солнышко!
My pleasure :-)
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
The best
Thank you! 😊
Awesome ❤️❤️❤️
Thanks 🤗