Your crazy software exported a file where the date column has the not-so-useful values like Jan, Feb, and Dec in a column. Episode 507 looks at a function to convert those values to real dates.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel podcast.
I'm Bill Jelen.
Have a really interesting problem send in today someone send in a data set and who knows where they downloaded this data set from, but unfortunately in the date column they have three letter month abbreviations.
So, it shows up saying May, December, December, March.
It's a little bit frustrated because you can't sort it correctly in the dates.
It'll put April before January and things like that interesting way to solve this problem.
I'm going to create a new column here called real date and basically. I want to change these months to real dates I'm going to use the DATEVALUE function. =DATEVALUE(, and Excel can't handle just a month like that, but if I put in the number one in quotes a space close quotes and then ampersand C2.
Basically, that's telling excel to give me the date value of the first of May that will convert to May first of this year, copy that down all the way and you'll see that converts December to December 1st and so on I can now sort this data and have January come to the top February, March and April again, that's the DATEVALUE function.
and instead of just using the word January or Jan one before it using the concatenation character.
So, quote one space quote ampersand C2 and Excel can accurately convert those two dates.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Have a really interesting problem send in today someone send in a data set and who knows where they downloaded this data set from, but unfortunately in the date column they have three letter month abbreviations.
So, it shows up saying May, December, December, March.
It's a little bit frustrated because you can't sort it correctly in the dates.
It'll put April before January and things like that interesting way to solve this problem.
I'm going to create a new column here called real date and basically. I want to change these months to real dates I'm going to use the DATEVALUE function. =DATEVALUE(, and Excel can't handle just a month like that, but if I put in the number one in quotes a space close quotes and then ampersand C2.
Basically, that's telling excel to give me the date value of the first of May that will convert to May first of this year, copy that down all the way and you'll see that converts December to December 1st and so on I can now sort this data and have January come to the top February, March and April again, that's the DATEVALUE function.
and instead of just using the word January or Jan one before it using the concatenation character.
So, quote one space quote ampersand C2 and Excel can accurately convert those two dates.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
