Ronika Khanna shows how to convert dates stored as text into real Excel dates using Text to Columns. When dates won't sort properly, go to Data → Text to Columns and pick the right date order like MDY. It works most of the time.
Ronika Khanna
Excel 365
Text to Columns wizard (Data → Text to Columns)
Hello everyone. Today I thought I would change things up a bit and do a short tutorial on Excel. This is a problem that I encounter frequently and I thought it might be useful for you to know the solution. The problem is what I have here is an export of data, of banking data, and this can be any kind of data that has a date in it. The date, although it looks like it's properly formatted, it actually isn't in a date format. And when I go to the functions here to convert it, which is an easy way of converting, you should always try this first because it will often just convert it from here from a number to the date format. So when I go here I know that it hasn't converted it because I want to sort this by date. So I click on my data has headers, I click on date and I click on OK and it gives me this warning and if this was in proper date format it would not give me this warning, which is to sort numbers and numbers stored as text separately or sort anything that looks like a number as a number.
So the fix for this in this particular case doesn't always work, but it does work a lot of the time is to go to text to columns and I'm in Excel 365, so it's the latest version of Excel, but you should be able to see this in most versions of Excel. And the function I'm looking for is text to columns. So I click on text to columns and I have some fields here. So I'm just going to click on next and I'm going to click on next again and then in the date little checkbox here I am going to click on that.
This NDY is a reflection of the format that the date column and the fields is already in. So you'll see that I have 11 11-2-2020 or you can see I have February 26, 2021. So it's NDY. If this was for example February.
If it was 2602, 2021, then I would choose DMY. So I'm going to choose MDY and then click on finish and you'll see there is a little movement here. It actually changed it to a properly formatted date and to verify that this is done I am going to try and sort it again and I will click on date, ok and you will see that it sorts it according to the date. I hope this was helpful. Please let me know if you have any questions in the comments or if you have ideas for other Excel videos. There are tons of them. On YouTube so you can almost always find your solution. But if there's anything specific that you're looking for, you can certainly let me know. And please subscribe for this and QuickBooks Online videos and other tax related stuff. Have a great day.
