Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Converting dates with text functions

From: Cleaning Up Your Excel 2010 Data

Video: Converting dates with text functions

There are many different ways in which you can enter a date data into an Excel worksheet; however, a lot of different layouts don't lend themselves to working with Excel dates efficiently. To work with dates efficiently in Excel, they must be considered values, so that we can work with them computationally. Date, the way we see it in column A and in column C isn't really set up properly. But if we've got a ton of entries like this, we want to be able to convert them into actual dates, and usually this takes a separate column.

Converting dates with text functions

There are many different ways in which you can enter a date data into an Excel worksheet; however, a lot of different layouts don't lend themselves to working with Excel dates efficiently. To work with dates efficiently in Excel, they must be considered values, so that we can work with them computationally. Date, the way we see it in column A and in column C isn't really set up properly. But if we've got a ton of entries like this, we want to be able to convert them into actual dates, and usually this takes a separate column.

Now, a function you wouldn't necessarily know much about, but relatively easy to use, is a function called date, and this function needs a year, month and day. Now the date in Column A doesn't have any days associated with it, so we'll just use the first day of the month. So what can we do with this information? From the right-hand side of each of those entries there, the four rightmost characters consist of the year. So here's a function called right, and its sole purpose is to pull data out of the right-hand side of these cells.

And we want to pull off the four rightmost characters, and that will constitute the actual year that we're looking for. Now we need the month, but recognize that some of them months, as in A2, are two characters, but in A3 and A4, it's one character. But it wouldn't pick up the space as well, so what do we need now? From the left side of cell A2, right there, comma, let's pick up two characters. That's going to give us the month. And now we need the day, comma. Let's just put in the number 1. A right parenthesis.

So what we're doing is gathering some of the information there and inserting 1 for the day to come up with a date, and this is what we see. And that looks good. We'll double-click. Check out the others here and there. Now if some of these dates, by the way, are impossible here, if somebody put in a 13th month or at a three-digit year here and there, we would have some other problems. That appears not to be the case. Now we're not quite finished with this, so what we also need to do in this example here is to take these results and either paste them back onto the original data--but let's say we want to hold on to that at least for the moment.

A quick way to convert these into actual values is, after selecting them all, use the right mouse button and drag, for example, upward and then downward right back on top of itself with the right mouse button. Copy Here as Values Only, so we see the data looking this like this. It's all cleaned up. And once you've seen one example like this--now let me press Ctrl+Z to undo. Once you've seen one of these--I'll make the column a bit wider--you begin to gather some sense of how you can pull together information from different locations. And the example in Column C is very similar.

I think once you saw that, you would realize we could easily put in Column D a function similar to this, a collection of various date, right, and left combinations to come up with dates there as well too. Column E, perhaps a little bit trickier, and you might even need to set up a table over here. And converting this into a date that Excel could use, what we need to do here is to somehow look up what those three letters are, or work with some other techniques. So, a quick look at this might be the following.

We need from here to gather the actual year, so once again we'll use this date function. We want to gather the year from the four leftmost characters, so we'll use the left characters from here. That's going to give us the year. Now, how do we get the month? The next thing we need to do is to pull out characters that start in the sixth position, reading from the left, and then three characters. So here's a function you might not have used. It's called mid.

And, by the way, if you want more information on how to use some of these text functions, you might want to check out the Excel 2010 or 2007 course on advanced formulas and functions, and learn more about text functions like Left and Mid. Think of the word middle here. What we're going to be doing is pulling out data from the middle here, and that's going to be starting in the sixth position from the left. So we're looking at cell E2, 6 position, Starting there, we want to pull out three characters.

Now in this particular case, we'll be looking at Sep. And now we want to use, by using by using vlookup, we want to take that Sep value, comma, and look it up in this list that we see over here, using a vlookup, comma. And then we want to get our data out of the second column of that list. That's a 2. So there's a lot going on here, and I wouldn't expect anybody to remember this. And if you haven't seen the other functions, this is quite a stretch all of a sudden.

Following the 2, then we need to put in a 0, indicating it's an exact match, and you should also see the word false down there. 0 is a little bit shorter. It also works. So that's going to give us the actual number of the month, comma. And now the actual day is going to be the two rightmost characters out of E2. And again, recognize that some of the other days that we see there have only one character, so that means we'll be using the space there. So the last thing we need here in this combination of functions here is to get the day out of that by choosing right, meaning from the right side of this cell, comma, 2, right parenthesis, right parenthesis.

And there is the date. That's the way we see it. And let me make this, as I scroll to the right, make the column a little bit wider, so we can see this even better. There is a lot going on there. The result tells the story more than anything. But a combination of functions, and this may seem a bit involved and pretty tricky, and yet the payoff is, a lot of times when you encounter dates like this, it's not just having 8 or 10, the way we see here, but thousands of these. And so it's worth the effort, and the work sometimes to turn these into dates that Excel really can use.

The one thing that needs to be changed here before we can copy this down the column is to make sure that as we copy this with the reference to are table data over there, which you do have to set up manually, is referenced right here. J2 to K13 must be made absolute, and you can do that by pressing the F4 key. And now, one more time here, this is complete, we'll double-click to copy this down the column. So at different times, you will need a variety of techniques, possibly using text functions, to convert unusual date formats into actual Excel dates that can be used computationally in your Excel worksheets.

Show transcript

This video is part of

Image for Cleaning Up Your Excel 2010 Data
Cleaning Up Your Excel 2010 Data

24 video lessons · 16554 viewers

Dennis Taylor
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now Already a member? Log in

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.


Mark all as unwatched Cancel

Congratulations

You have completed Cleaning Up Your Excel 2010 Data.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.