Easily Fix Dates Formatted as Text with Power Query

dates incorrectly formatted as text is one of the most common problems we face in Excel so in this video I'm going to show you how easy it is to fix them using power query and the nice thing about this technique is it can be easily refreshed to pick up new dates be sure to watch to the end where I cover a date problem that comes up all the time when importing data from CSV or text files that contain us formatted dates here I've got five common incorrect date formats now the problem with these dates is they're text and Excel cannot work with text dates in formulas or group them in pivot tables among other things and there are a few ways we can tell these dates are text but one of the simplest is to use the keyboard shortcut the control key with the back quote symbol it's typically found in the top left of your keyboard and it shares the same key as the tilder symbol this keyboard shortcut removes any formatting and it will show you formulas rather than their results and what we're looking for here is the dates and times to display in their date serial number format so if I correctly enter a date and press enter what I see is the date serial number and if I enter a date and a time let's say 1 p.m. it shows the time in the decimal portion of the date now I'm not going to go into any more detail here on how Excel works with dates and time but if you want to learn more there's a link in the video description to my comprehensive Excel date and time tutorial so it's clear that I need to fix all the dates here so I'm going to press control and the back quote symbol again let's delete those dates I don't need them anymore before I get started I want to point out that my date format is day month year so that's what I'll be working with in this example but if your date format is month day year these techniques will automatically adapt to your system settings for your location so you don't need need to do anything different notice that my dates are already in Excel tables so all I need to do is go to the data tab of the ribbon if you have Excel 2010 or 2013 you'll go to the dedicated power query Tab and then from table SL range this opens the power query editor where we can fix the date format you can see in the applied steps power query has already applied a change type step and it's set the data type to a number which is what the one three indicates I need to delete that change type step because it's not correct now there's the hard way of fixing these dates and the easy way I'm going to cover the hard way first because this can be used for almost any date format so it's a handy back stop skill to have I'll start by splitting the text into the three date components day month and year so on the Home tab we want split column by positions so I want to split it at the first position the second and the fourth click okay now we have three columns one for my day one for the month and one for the year again it's automatically applied the change type step and it set the data type as a number which is perfect that's what I need to create my date now there are a couple of ways you can merge the dates back together one is to add a custom column using the date function now the date function requires the year which is in column 3 then the month and then the day and let's click okay and now we have the date all I need to do is change the data type to date and it's good to go the other way is to select the first column hold down shift select the last column and then we're going to merge The Columns so we can add a merge column or you can simply transform the existing columns it doesn't really matter here I'm going to choose a custom separator the standard date separator is the forward slash I'm going to leave the col column name is merged I'm going to delete it in a moment anyway so I'll click okay now we have the columns merg back together it looks like a date but let's set the data type as date and now it's good to go so there's a couple of ways to create a proper date from an incorrectly formatted date by splitting out the components and then joining them back together but there's an even easier way so I'm going to go back and delete all of the previous steps so we're back to our text and then on the add column tab I'm going to do column from examples now make sure if you have more than just one column of data which is probably likely that you first select the column that you want to fix and then choose column from examples from selection which means the currently selected column here I'm going to type in the first example so I want 06 06/2020 and then down arrow and then let's give it another example that hasn't recognized the pattern yet and there we go it's recognize the pattern we can see up here the formula that it's going to insert for me all I need to do is click okay there's my date let's change the data type to date we can get rid of this column now and rename this one date and I'm ready to close and load so on the Home tab I'm going to close and load to we'll put it just below the original table click okay and there's our correctly formatted dates let's do the control and the back quote test we can see they're correctly entered as date serial numbers so control and back quote again let's go to example number two again I'm going to load this data to power query and Tada it's automatically detected that this is dates and set the data type accordingly which has formatted the dates for me in day month year but what if I wanted the dates displayed how they were originally that is with the month name name then the day then the year Well formatting like this isn't done in power query and a lot of people get stuck trying to format data inside a power query when the place for formatting is in the Excel grid so I'm going to close and load two and let's do the formatting in the grid pop it below and click okay so all I need to do if I want to format it so the month name is visible is open the format dialog box control1 let's create a custom format and here we want month day comma and the year I'll click okay and now we have our correctly formatted date serial numbers displaying the month name then the date then the year let's load the next example data from table range this time I'm going to use the column from examples again and we'll just start by typing in a six notice it's brought up a list of possible trans Transformations that I might want and the one that I want is actually the second one so select that and press enter and tab it's automatically entered the formula up here and I can see by the preview that it's correct so I'll click okay let's get rid of the original column just press delete and we'll rename this one and we're good to close and load so you can see it's super easy sometimes just depends on the format the next example contains dates and time let's load them to power query and we'll see how to tackle this and there you go I haven't had to do anything it's correctly fixed the data type as date and time all I need to do is close and load two choose where I want to put it and click okay notice the default formatting in Excel is using 24hour time but again I can fix this with a custom format all I need to do is add on AM/PM and you can see in the preview it's going to format it in the correct time for me I can also change this to show the month name if I prefer I'm going to leave it as is and there we have date and time correctly formatted again control and the back quote shows me the date time serial numbers now the next example shows the dates here are actually in month day year format let's take a look at how power query handles this notice the automatic change type step has set the type as text we can tell that by the ABC in the column header this is clearly not right my local is set to English Australia so powerquery can only automatically recognize dates that match my date format of day month year so I'm going to delete that step now the way we fix dates that are in the wrong local is click on the icon in the top left then using local this allows us to choose the local of the source data so the data type here I want is date/time and the local is the loc where the data came from so that is United States so what we want is English United States click okay and power query takes that us date format and converts it into my local format which is day month year let's close and load two and we'll pop it below the last example I want to show you is when you import data Maybe from a CSV file or a text file and the dates are formatted for a different local for example here I have a CSV file containing dates and if we open it in notepad you can see it's a list of dates for January but it's in the US date format of month day year now normally your CSV or text files are going to contain more than just a column of dates I've just kept it simple for this example let's take a look at what happens when we open this file in Excel let's make the column wider and we can see what's going on now you can see the first 12 dates have been formatted as a date data type and if we press controll and the back quote you can see they are date serial numbers however if we go back and we format them to show the month name let's give them a custom format day month year you can see they're not correct because this should be a list of January dates and then of course we have the list of dates after January 12th that are still in a text format so what's happened here is Excel has assumed that the first 12 dates in the CSV file are dates in my local format of day month year and it's applied the date data type but then it's got to the next set of dates and it's gone hold on there's only 12 months in the year these can't be dates this must be text and this is a really messy problem because often your data has this combination of dates scattered in no particular order throughout your data thankfully it's easily fixed instead of opening the CSV or text file directly into Excel you use power query to get the file so I'm going to close this and not save it and then on the data tab of the ribbon from textcsv there's my file I'm going to import it it it gives me a preview now I need to transform it so that I can set the data type to the correct local let's promote the first row as the header and then all I need to do is click on the icon here and choose using local in the data type I want date and in the local it's English United States that is the local that is the origin of the data I'll click okay and now power query has converted the dates from us dates into my local date format which is day month year and now I can simply close and load and we'll leave it go into a table on a new worksheet and there I have my dates ready to work with in the correct date format and we can confirm their date serial numbers with control and back quote I hope you found this technique useful you can download the Excel file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 96,394

Keywords: power query, excel power query, fix text dates in excel

Id: 0RN3FZv3w84

Length: 12min 48sec (768 seconds)

Published: Wed Oct 21 2020