Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great question sent in by Brett. Brett sent it from Australia.
He has a series of dates, so January 1st 2010 here, and then he used this great formula =DATE of the YEAR of the date above us and 1, MONTH of the date above us and 1.
And when we copy that down, it accurately will fill in the first of each month, including, I'm always amazed, here in January, where it's actually asking for the first of the 13th month of 2010, and it has no problem with that.
Now Brett went through and formatted this, Ctrl+1 will format it as a Date, where we show 3-digit month name, so mmm yyyy, January 2010, alright, so far so good.
But, he needs to do a VLOOKUP back in the report, and he needs to have the words “Opening Balance” appear.
And so he came here and, he joined “Opening Balance”& alright, and then everything else changed into #VALUE!
Error, even when he copied that down.
He said “Alright, how can I make those words appear and have it all work out?” So, here's the solution that I have, we're going to back into the Custom Number Format.
So Ctrl+1, and here in the custom number format, in quotes we're going to put Opening Balance, and the date code that we used before, and that will get it to appear correctly.
Now, unfortunately, if you're actually going to do a VLOOKUP for this, the VLOOKUP is not going to work very well.
We're going to have to do =VLOOKUP, and here in the first part, what are we going to look up?
We’re going to look up the TEXT of this cell, comma, and then repeat here the custom number format from before.
So “” to get a single quote to appear, Opening Balance, “” to finish that off, and then mmm yyyy.
And what this function is going to do, is convert that date into something that looks like Opening Balance January 2010, and then, when he goes back to his table that has those actual amounts, it's going to work.
So there you have it.
very complicated solution to something that should be relatively easy.
If you just needed it to look right, Custom Number Format will do it correctly, but then to get that VLOOKUP to work, use the TEXT function.
Alright, I want to thank you for stopping by, thank Brett for sending that question in, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Great question sent in by Brett. Brett sent it from Australia.
He has a series of dates, so January 1st 2010 here, and then he used this great formula =DATE of the YEAR of the date above us and 1, MONTH of the date above us and 1.
And when we copy that down, it accurately will fill in the first of each month, including, I'm always amazed, here in January, where it's actually asking for the first of the 13th month of 2010, and it has no problem with that.
Now Brett went through and formatted this, Ctrl+1 will format it as a Date, where we show 3-digit month name, so mmm yyyy, January 2010, alright, so far so good.
But, he needs to do a VLOOKUP back in the report, and he needs to have the words “Opening Balance” appear.
And so he came here and, he joined “Opening Balance”& alright, and then everything else changed into #VALUE!
Error, even when he copied that down.
He said “Alright, how can I make those words appear and have it all work out?” So, here's the solution that I have, we're going to back into the Custom Number Format.
So Ctrl+1, and here in the custom number format, in quotes we're going to put Opening Balance, and the date code that we used before, and that will get it to appear correctly.
Now, unfortunately, if you're actually going to do a VLOOKUP for this, the VLOOKUP is not going to work very well.
We're going to have to do =VLOOKUP, and here in the first part, what are we going to look up?
We’re going to look up the TEXT of this cell, comma, and then repeat here the custom number format from before.
So “” to get a single quote to appear, Opening Balance, “” to finish that off, and then mmm yyyy.
And what this function is going to do, is convert that date into something that looks like Opening Balance January 2010, and then, when he goes back to his table that has those actual amounts, it's going to work.
So there you have it.
very complicated solution to something that should be relatively easy.
If you just needed it to look right, Custom Number Format will do it correctly, but then to get that VLOOKUP to work, use the TEXT function.
Alright, I want to thank you for stopping by, thank Brett for sending that question in, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
