In this dueling Excel podcast, the goal is to use a formula to convert 20131220 to a real Excel date. We'll meander through the usual suspects: Text to Columns, DATE(MID), Flash Fill and then be amazed as Mike provides a crazy TEXT function to solve the problem.
Transcript of the video:
Hey! Welcome back is dueling Excel podcast, episode 145.
Date-ify 20131220 with "0000\/00\/00" Live from Highline Community College, a time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel and here with Mike: Excel Is Fun.
MrExcel: Mike Girvin from his Excel Is Fun.
This is our episode 145.
How do we convert these strange dates in their real dates.
All right! So, someone has dates here.
This is year, month, day, and we need to convert those into real dates.
So, I'm gonna use [ alt D E ], data text to columns, doesn't matter what I choose here.
We're going to make sure that there are no de-limiters, and in step 3 simply say that it is Y M D.
Like that and here we will even move this out to B4.
So, we can see the before and the after click [ finish ] and we get the real dates.
They're using data text to column.
Mike: Wow! That is the trick. That's the point right man.
MrExcel: You know the first time, I tried that is like wow! I can't believe.
It was just a desperation move on there's no way that this will work.
I figured it's going to have to come out down here and use the equal date function and for the year, use the LEFT of this comma 4, now I wonder do I have to add 0 to that I'm going to try it without, we might have to come back and then the mid of this comma 5 comma 2, that means starting position 5 for length of two and then the RIGHT of that comma 2, closing parenthesis, make sure the last parenthesis black.
I learned that from Mike and we get the correct answer, double click to shoot it down.
So, there's two different ways to go Mike, I'll see what you have.
Mike: Wow! I love that text to columns.
That is the only way to do well, if I have to do one without a formula one, with the formula.
I'm going to use the the bulky and difficult Flash Fill and I actually learned this from MrExcel's book.
Sometimes because you have 07, there's a zero there.
If you try to Flash Fill and put 714 right, so if I were to 7/14/2001 and tried to flash fill that with data.
Flash fill or [ control E ].
It wouldn't get it right.
MrExcel: Control E.
What the heck, what is control E?
Mike: Control E, that's flash fill, so I learned this from MrExcel.
Control and I'm going to format this first to help it understand that lead 0, so I'm gonna come down to custom, M and that will give me the lead 0, /DD/YYY Now, I'm going to have to give it two examples, so 07/14/2001 and I'm going to have to choose my second example, 11/15/2009.
MrExcel: Why'd you leave the blank?
Mike: I'm picking two examples, there's 1 1 and a 07, I'm thinking it needs to and then [ control E ] and sure enough that work.
Hey! You can I would never use flash fill for this, anytime there's a lead 0 times, also give you a trouble, but if you mess around long enough, then you figure maybe you need some formatting and a couple examples.
There you go.
MrExcel: There you go yeah! Chad for Microsoft, you put flash fill together said that with numbers, it was really, really tough to try and figure out what the pattern is.
It's much easier when you have words for flash fill, instead of numbers.
Mike: So, yeah!
Why would you ever do flash fill when you have text to columns.
MrExcel: Text to columns, year, year, month, YMD fromat.
Mike: Hey! We're going to do the text function and we're going to take this value, and then add a number format.
Now, we really want...
MrExcel: There's no way that the text function is going to solve this.
How are you solve this with a text function?
Mike: No, we can do it with the text function away.
Yeah, we're going to take this and we're going to add a custom number formatting, check this out.
We really want "mmddyyy".
But we can't do that, I'm going to put 0 0 and I need to insert a slash(/), I'm going to use back slash(\), forward slash, the backslash, says whatever comes next, I'm going to insert this character then 00 and then my \/ back slash, forward slash and then 0 0 0 0 for the, up here.
MrExcel: Wait a second, first off I love this is going to confuse my co-workers.
They're going to not understand what the \/ is, but that data that you have is YMD, so you need four zeros before the first \/.
To get the year, right.
Mike: You know, this is gonna work.
Let's try it.
MrExcel: This is gonna hurt us.
Mike: Yeah! Sure it is.
MrExcel: You need a comma okay, but even with the comma Mike: It's definitely gonna work, look it that.
MrExcel: No, it's the year 0714.
Put four zeros.
Four zeros first, in the first batch four zeros.
Any of five 00 this before because that's gonna look like a date.
Mike: Like it's gonna be crazy.
MrExcel: And use the date value.
Mike: Watch this, now I'm gonna add zero, sure.
Look at that [ control 1 ], what? It's actually gonna work.
MrExcel: Day away!
MrExcel: He sets me up.
You know, I got up at 6 a.m. this morning, I flew out here and Mike says oh yeah, go ahead, we'll do this duel, he lets me do the the good ways right and then here at the end.
He throws this insane formula in.
Mike: Hey! I didn't, I'm not that smart.
I'm just a guy having fun with Excel.
I learned this from Barry Houdini.
MrExcel: Very easy.
Mike:Barry Houdini at the MrExcel Message Board, the most amazing place that all, actually...
Child: You don't told me about this.
Mike: It's amazing actually, I saw him do it with a time and just add a kick the other day, I tried it with the day and I was like, you gotta be kidding me.
MrExcel: That's crazy, already this trip to Seattle's worth it for that trick, right there.
Back with the point to me, point to you for that.
That's crazy, and a point to Barry Houdini for that cool trick, all right!
Mike: No, no, I'm gonna give a point to you for text to columns and then 10 points to Barry Houdini.
MrExcel: That's good alright!
That was that was amazing, alright.
Well I want thank every one for stopping by, we'll see you next time for the dueling Excel podcast from MrExcel and Excel Is Fun.
Date-ify 20131220 with "0000\/00\/00" Live from Highline Community College, a time for another dueling Excel podcast.
I'm Bill Jelen from MrExcel and here with Mike: Excel Is Fun.
MrExcel: Mike Girvin from his Excel Is Fun.
This is our episode 145.
How do we convert these strange dates in their real dates.
All right! So, someone has dates here.
This is year, month, day, and we need to convert those into real dates.
So, I'm gonna use [ alt D E ], data text to columns, doesn't matter what I choose here.
We're going to make sure that there are no de-limiters, and in step 3 simply say that it is Y M D.
Like that and here we will even move this out to B4.
So, we can see the before and the after click [ finish ] and we get the real dates.
They're using data text to column.
Mike: Wow! That is the trick. That's the point right man.
MrExcel: You know the first time, I tried that is like wow! I can't believe.
It was just a desperation move on there's no way that this will work.
I figured it's going to have to come out down here and use the equal date function and for the year, use the LEFT of this comma 4, now I wonder do I have to add 0 to that I'm going to try it without, we might have to come back and then the mid of this comma 5 comma 2, that means starting position 5 for length of two and then the RIGHT of that comma 2, closing parenthesis, make sure the last parenthesis black.
I learned that from Mike and we get the correct answer, double click to shoot it down.
So, there's two different ways to go Mike, I'll see what you have.
Mike: Wow! I love that text to columns.
That is the only way to do well, if I have to do one without a formula one, with the formula.
I'm going to use the the bulky and difficult Flash Fill and I actually learned this from MrExcel's book.
Sometimes because you have 07, there's a zero there.
If you try to Flash Fill and put 714 right, so if I were to 7/14/2001 and tried to flash fill that with data.
Flash fill or [ control E ].
It wouldn't get it right.
MrExcel: Control E.
What the heck, what is control E?
Mike: Control E, that's flash fill, so I learned this from MrExcel.
Control and I'm going to format this first to help it understand that lead 0, so I'm gonna come down to custom, M and that will give me the lead 0, /DD/YYY Now, I'm going to have to give it two examples, so 07/14/2001 and I'm going to have to choose my second example, 11/15/2009.
MrExcel: Why'd you leave the blank?
Mike: I'm picking two examples, there's 1 1 and a 07, I'm thinking it needs to and then [ control E ] and sure enough that work.
Hey! You can I would never use flash fill for this, anytime there's a lead 0 times, also give you a trouble, but if you mess around long enough, then you figure maybe you need some formatting and a couple examples.
There you go.
MrExcel: There you go yeah! Chad for Microsoft, you put flash fill together said that with numbers, it was really, really tough to try and figure out what the pattern is.
It's much easier when you have words for flash fill, instead of numbers.
Mike: So, yeah!
Why would you ever do flash fill when you have text to columns.
MrExcel: Text to columns, year, year, month, YMD fromat.
Mike: Hey! We're going to do the text function and we're going to take this value, and then add a number format.
Now, we really want...
MrExcel: There's no way that the text function is going to solve this.
How are you solve this with a text function?
Mike: No, we can do it with the text function away.
Yeah, we're going to take this and we're going to add a custom number formatting, check this out.
We really want "mmddyyy".
But we can't do that, I'm going to put 0 0 and I need to insert a slash(/), I'm going to use back slash(\), forward slash, the backslash, says whatever comes next, I'm going to insert this character then 00 and then my \/ back slash, forward slash and then 0 0 0 0 for the, up here.
MrExcel: Wait a second, first off I love this is going to confuse my co-workers.
They're going to not understand what the \/ is, but that data that you have is YMD, so you need four zeros before the first \/.
To get the year, right.
Mike: You know, this is gonna work.
Let's try it.
MrExcel: This is gonna hurt us.
Mike: Yeah! Sure it is.
MrExcel: You need a comma okay, but even with the comma Mike: It's definitely gonna work, look it that.
MrExcel: No, it's the year 0714.
Put four zeros.
Four zeros first, in the first batch four zeros.
Any of five 00 this before because that's gonna look like a date.
Mike: Like it's gonna be crazy.
MrExcel: And use the date value.
Mike: Watch this, now I'm gonna add zero, sure.
Look at that [ control 1 ], what? It's actually gonna work.
MrExcel: Day away!
MrExcel: He sets me up.
You know, I got up at 6 a.m. this morning, I flew out here and Mike says oh yeah, go ahead, we'll do this duel, he lets me do the the good ways right and then here at the end.
He throws this insane formula in.
Mike: Hey! I didn't, I'm not that smart.
I'm just a guy having fun with Excel.
I learned this from Barry Houdini.
MrExcel: Very easy.
Mike:Barry Houdini at the MrExcel Message Board, the most amazing place that all, actually...
Child: You don't told me about this.
Mike: It's amazing actually, I saw him do it with a time and just add a kick the other day, I tried it with the day and I was like, you gotta be kidding me.
MrExcel: That's crazy, already this trip to Seattle's worth it for that trick, right there.
Back with the point to me, point to you for that.
That's crazy, and a point to Barry Houdini for that cool trick, all right!
Mike: No, no, I'm gonna give a point to you for text to columns and then 10 points to Barry Houdini.
MrExcel: That's good alright!
That was that was amazing, alright.
Well I want thank every one for stopping by, we'll see you next time for the dueling Excel podcast from MrExcel and Excel Is Fun.
