Handling Dates with unusual formats

- When you get data from other sources, sometimes the way dates are entered are a bit unusual. In column A, we're seeing some data entries here, not typical. In columns B, C, and D, we're putting in entries that could be interpretations of how that data really should have been entered. Now, it's highly unlikely, with certain sets of data, would you ever have data covering the year 2011 and also the year 1920. But as you look at the numbers, you could see how that could be derived; 1920, the year 1920, is the middle four digits here.

And there's a 12 in front of it. There's an 11 on the end of it. So it could be interpreted as December 11th of that year or November 12th of that year. We see some other entries here and how they might be interpreted. And, down here in rows nine, 10, and 11, we're looking at recent dates; 2011, but even there, we can see two different interpretations of the same data. This entry down here actually has three different interpretations. Is that from the year 2020? That's where the year is in the middle of the data.

Or is it the year 2011? The last four characters of the data. If you make entries like this with no slash or no dash, they can be worked with in Excel, but sometimes, at first, it's not clear exactly how to approach these. If you enter data with a slash or a dash, usually you don't have major difficulties. But I think the rationale behind putting numbers in like this could be it's a space saver, since we're not using slashes or dashes. Now, the various ways that we could enter this data could be, and I've depicted them over here, there's six different ways: month, day, year; day, month, year; year, month, day; and so on.

There's six variations here. And I've actually set these up manually ahead of time. Now, the issue is, you're going to see, perhaps, one of these. You're not likely to see two of these in the same list of data. But how do we work with these? How do we work around them? Two ways. One is by way of the DATE function. Not necessarily the best, but it does bring up some other issues that we might want to consider. Let's say we've got some data that looks like this. Now, right now, these are actually put in as formulas. I built these by using the data over in columns F, G, and H.

And I want to change these into actual values, so I'll highlight column J. And, with the right mouse button, very quickly, I'll simply, in one fluid motion, drag this into another column. Say, leftward, into column I. Then right back on top of itself, using the right mouse button. And simply Copy Here as Values Only. The little green indicators in the upper-left corner of each cell, by clicking the arrow you can see what it means: Number Stored as Text. And that's what these are at the moment. Now, how could we work with these? One way is to use the function called "DATE." The DATE function expects to see year, month, day.

If we're trying to get that information out of cell J2, we first need to get the year. And we can do that by using the function called "RIGHT." We're going to be looking at this cell: J2 comma, and pick up the 4 rightmost characters. That will give us the year. And now we need to pick up the month: that's going to be the two leftmost characters. So we use the function called "LEFT" to pick up data from the left side of the entry. And here, it's 2 characters. Finally, we need to pick up the day.

That's the third and fourth characters. So we use the function called "MID." Think of the word "middle," although often not literally the middle. The "MID" function says I'm looking somewhere. J2 comma, let's start in the 3rd position from the left and pick up 2 characters. So that's going to give us the day. And, putting this all together, we could convert that entry in J2 into this date. And, similarly, but using these in different orders, we could pick up data from column K, column L, or column M if the data happened to appear that way.

So that's certainly one option. But a much better option is to simply use the feature that typically splits data into different columns. And that's the feature called "Text to Columns," found on the DATA tab. So let's say we want to convert this. We don't need to worry about having extra space, extra columns on either side, empty columns, to work with this. We will simply click column J, go to the Text to Columns feature. In the Convert Text to Columns Wizard, Step 1, choose Delimited, then Next, and Next.

Step 3, choose Date. And here are the six variations that the data might be laid out in. And we're going to be using MDY; left to right: month, day, year. Select that. And then Finish. And there's the data, looking pretty good. Looking the way we want it to be. And these are workable dates. They've been converted. Notice in the formula bar, no formulas or anything. We've changed this. And we could do this for all the other remaining five columns.

I'll just pick one of them at random. Maybe this one out here: MYD. That's an unusual layout, having the year in the middle. But, nevertheless, we can use the same capability here. We go to Text to Columns, Delimited, Next, Next; choose Date. In this case, not MDY but MYD: month, year, day, left to right. Click Finish. And we've got our dates in place. Same dates we're seeing over in column J, but the layout was different, that we started with.

So we've seen valuable techniques here for converting unusual date formats into standard date layouts, using Text to Columns. The DATE function occasionally has its uses. You might explore that a little bit. But I think the solution, by far, in this example, and we've seen them, two different cases here, converting these entries into dates, by way of Text to Columns.