Join Dennis Taylor for an in-depth discussion in this video Handling dates with unusual formats, part of Excel Tips Weekly.
- 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.
Author
Updated
12/10/2019Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Excel: Tips and Tricks
with Dennis Taylor4h 20m Intermediate -
Visio Tips and Tricks
with Scott Helmers1h 49m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Handling dates with unusual formats