We have a series of Dates. We want to show quarters for the year 2012. Bill 'MrExcel' Jelen goes Old School on this one while Mike 'ExcelisFun' Girvin goes for a more Functionally 'stream-lined' approach.
Mike and Bill look at the =CEILING, =MONTH, VLOOKUP functions and more.
Dueling Excel Podcast #97...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Mike and Bill look at the =CEILING, =MONTH, VLOOKUP functions and more.
Dueling Excel Podcast #97...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Hey, welcome back. It's another dueling excel podcast.
I'm Bill Jelen from MrExcel be joined by Mike Gel Girvin from Excel Is Fun This is our Episode 97 - Quarters Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question.
Well, let me just, it's simple. We have dates here.
We want to show a quarter right.
So its Q4 on this case Q3-2012 That's the goal. We want to get that format. Oh, you know there's a all kinds of ways to use. The MOD and and TEST for it, but I'm just going to go old school here ="Q" & CHOOSE CHOOSE We want to choose based on the month of this date.
The month is going to give us a number from 1 to 12 and CHOOSE is great when we're choosing items that are numbered 1 through I think up to 30 or I don't know it might go beyond that.
Now so if it's January February March, it's quarter one, so I'm going to put 1, 1, 1, and then if it's the next three months, it's quarter 2 and then 4, 4, 4 Alright, so what that is going to do that's going to get us the quarter.
We will double click to shoot that down.
Just make sure that's working Q2 Q1. That's beautiful &"-"&Year of A2 Since out of edit mode there are to press Q or F2 to get back into edit mode.
Alright, so I think that looks like what it looks like now.
This is easier if your Fiscal year ends on December 31st.
It's a lot tougher if you have a Fiscal year that end some other period than we got to kind of that year it has to test to see if it's before or after the ending month but in this particular case for Danny Chacko 1983 I think this will do it.
Mike. Let's see what you have.
Thanks MrExcel. Man. I love this CHOOSE. It is just straight and to the point.
I guess I'm going to copy this exact for me except for I'll do something slightly different, right in the middle part of this.
Now I'm going to use to start off with a MONTH function right and get a number 1 to 12 Double click and send it down, but that's not what I want. I want 1 to 4. Well. I need to somehow do that so I'm going to use the CEILING function.
Now CEILING is a type of rounding function that always rounds up to some significance level. So I'm going to say round up always to the 3, so this will give me 9 3 6 12. So those are all the possibilities.
Now I basically have what I want. I just have to divide by 3 and then I have my Oh that's number date formatting. I'm going to use Ctrl + Shift+ ~ that applies the general formatting so there I have it.
I have my 1 2 3 4 and that's really the hard part of this.
The rest of this is in "Q" & to join that.
We have our quarter 1 2 3 4 and and then we &" -" so in double quotes we have some text &YEAR.
Double click and send that down, so CEILING MONTH and a little bit of rounding up and division instead of that CHOOSE.
Either one is pretty good. You know for this problem though, if you're just a rank beginner, and you don't know CHOOSE and CEILING and all that.
You know I think a lot of people might just do something like this.
I can scoop this over here.
Just flat-out type some stuff into some cells right.
It takes just you know less than a minute or something like that come over here and just do a straight VLOOKUP. I'm looking up the date comma let's see if I can ah like this a little bit more.
Looking up the date right here notice this table is sorted from smallest to biggest those are just numbers there, so it will always get the right one. I say. Hey, That's the table F4 comma I want to return the thing. I typed in in the second column, so I put a 2.
Close parenthesis.
Right, so.
CHOOSE CEILING or just flat out a VLOOKUP.
All right throw it back to MrExcel.
I love these dueling podcasts. See I learn something every single time.
When I was doing mine I kind of said well, hey look we got to take the month and divide by three.
But then that gives us a 0 here, so I said all right well, then we're going to add 1 to the 3, but when I copied that down It didn't work because 3/3 is instead of a fraction, it actually becomes 1 so the integer doesn't take it back down.
It's you know, we're getting groups of 3 here but it's off by 1 so I said ah jeez then we have to do this =IF the MOD of that divided by 3. If the remainder is 0 then we add 0 instead of 1 and then I finally had the answer and then Mike just comes along and blows it all out of the water with the CEILING function. Oh my gosh That's incredible.
But what I loved the most about Mike, I'm going to Ctrl+ Page down here, huh is just casually he mentions. Oh whoops. I got the answer in the wrong date format and all I have to do is press Ctrl+ Shift.
Till day frankly I didn't hear what he said after that my mind was just reeling that there is an easier way to convert those dates back to numbers.
So a hundred points to Mike for that cool tip.
Hey, Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel be joined by Mike Gel Girvin from Excel Is Fun This is our Episode 97 - Quarters Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question.
Well, let me just, it's simple. We have dates here.
We want to show a quarter right.
So its Q4 on this case Q3-2012 That's the goal. We want to get that format. Oh, you know there's a all kinds of ways to use. The MOD and and TEST for it, but I'm just going to go old school here ="Q" & CHOOSE CHOOSE We want to choose based on the month of this date.
The month is going to give us a number from 1 to 12 and CHOOSE is great when we're choosing items that are numbered 1 through I think up to 30 or I don't know it might go beyond that.
Now so if it's January February March, it's quarter one, so I'm going to put 1, 1, 1, and then if it's the next three months, it's quarter 2 and then 4, 4, 4 Alright, so what that is going to do that's going to get us the quarter.
We will double click to shoot that down.
Just make sure that's working Q2 Q1. That's beautiful &"-"&Year of A2 Since out of edit mode there are to press Q or F2 to get back into edit mode.
Alright, so I think that looks like what it looks like now.
This is easier if your Fiscal year ends on December 31st.
It's a lot tougher if you have a Fiscal year that end some other period than we got to kind of that year it has to test to see if it's before or after the ending month but in this particular case for Danny Chacko 1983 I think this will do it.
Mike. Let's see what you have.
Thanks MrExcel. Man. I love this CHOOSE. It is just straight and to the point.
I guess I'm going to copy this exact for me except for I'll do something slightly different, right in the middle part of this.
Now I'm going to use to start off with a MONTH function right and get a number 1 to 12 Double click and send it down, but that's not what I want. I want 1 to 4. Well. I need to somehow do that so I'm going to use the CEILING function.
Now CEILING is a type of rounding function that always rounds up to some significance level. So I'm going to say round up always to the 3, so this will give me 9 3 6 12. So those are all the possibilities.
Now I basically have what I want. I just have to divide by 3 and then I have my Oh that's number date formatting. I'm going to use Ctrl + Shift+ ~ that applies the general formatting so there I have it.
I have my 1 2 3 4 and that's really the hard part of this.
The rest of this is in "Q" & to join that.
We have our quarter 1 2 3 4 and and then we &" -" so in double quotes we have some text &YEAR.
Double click and send that down, so CEILING MONTH and a little bit of rounding up and division instead of that CHOOSE.
Either one is pretty good. You know for this problem though, if you're just a rank beginner, and you don't know CHOOSE and CEILING and all that.
You know I think a lot of people might just do something like this.
I can scoop this over here.
Just flat-out type some stuff into some cells right.
It takes just you know less than a minute or something like that come over here and just do a straight VLOOKUP. I'm looking up the date comma let's see if I can ah like this a little bit more.
Looking up the date right here notice this table is sorted from smallest to biggest those are just numbers there, so it will always get the right one. I say. Hey, That's the table F4 comma I want to return the thing. I typed in in the second column, so I put a 2.
Close parenthesis.
Right, so.
CHOOSE CEILING or just flat out a VLOOKUP.
All right throw it back to MrExcel.
I love these dueling podcasts. See I learn something every single time.
When I was doing mine I kind of said well, hey look we got to take the month and divide by three.
But then that gives us a 0 here, so I said all right well, then we're going to add 1 to the 3, but when I copied that down It didn't work because 3/3 is instead of a fraction, it actually becomes 1 so the integer doesn't take it back down.
It's you know, we're getting groups of 3 here but it's off by 1 so I said ah jeez then we have to do this =IF the MOD of that divided by 3. If the remainder is 0 then we add 0 instead of 1 and then I finally had the answer and then Mike just comes along and blows it all out of the water with the CEILING function. Oh my gosh That's incredible.
But what I loved the most about Mike, I'm going to Ctrl+ Page down here, huh is just casually he mentions. Oh whoops. I got the answer in the wrong date format and all I have to do is press Ctrl+ Shift.
Till day frankly I didn't hear what he said after that my mind was just reeling that there is an easier way to convert those dates back to numbers.
So a hundred points to Mike for that cool tip.
Hey, Will see you next week for another dueling excel podcast from MrExcel and Excel Is Fun.
