This week, Bill is in New York City to judge the ModelOff Worldwide Excel Championship! Today, Bill looks at "Dates To Quarters" with Devin Rochford.
Working with Years and need to bring those dates to Quarters? Follow along with Episode #1611, Devin Rochford shows us a clear-cut method to work this out painlessly.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, 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! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Working with Years and need to bring those dates to Quarters? Follow along with Episode #1611, Devin Rochford shows us a clear-cut method to work this out painlessly.
ModelOff is a worldwide competition to find the best Excel modellers with a $25,000 cash prize. For information on next year's competition, visit http://www.modeloff.com/modeloff-2013/
...This blog is the video podcast companion to the book, 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! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
Bill Jelen: Hey, welcome back to the MrExcel podcast, I'm Bill Jelen.
This is great, a whole week where I don’t have to come up with the ideas.
I’m here at the ModelOff World Modeling Championships in New York City.
Now, our next contestant got a wonderful all-expense paid trip to New York from, New York.
Yeah here's Devin Rockford with Dates to Quarters, all right Devin.
Devin Rockford: Hi guys I'm Devin Rockford, I work with Ernst & Young in the transaction advisory services and one big problem that I always have is displaying quarters, quarters and years in Excel.
Typically we get stuff formatted with dates and this is a quick easy solution to take care of that.
So the first thing you do is you get your "Q".
You open parenthesis, you close parentheses, Q it displays as a Q -- and then you can do a ROUNDUP function, and Q open parenthesis.
And now you want to reference your month, so you go to a month; reference your date, reference date, close that.
="Q"&roundup(month(B2)).
Bill Jelen: Okay now, I'm going to interrupt -- so F2 there you, had to hit F2 because you were not in Point Mode right?
Devin Rockford: Exactly.
Bill Jelen: And so, when you hit the up arrow it wasn't moving up.
F2 is a great way to go back into that cool point with the mouse.
All right, go ahead.
Devin Rockford: Exactly.
And then you divide that by 3.
So now what you're doing is for instance, in this one it takes 12 divided by 3; which innately enough equals 4.
But if it was 11, it would round up from; it would round up from 3.66667 to 4.
Bill Jelen: We have to tell how many digits there?
You have to tell the number of digits.
Devin Rockford: Oh yeah sorry, you do need to tell the number of digits.
Bill Jelen: Comma- zero (,0).
There we go.
Devin Rockford: Comma- zero (,0).
Bill Jelen: Q4, that’s hot.
Devin Rockford: And that shows Q4.
Bill Jelen: Okay.
Devin Rockford: The next part is very, very easy.
If you want to put a little, a little hyphen in there you just go, and open parentheses; there you have your dash.
And then, all you need to do then is reference a year, and all you have to do is a text function -- go back into enter mode, reference the date again and YY which will format it as about two numbers of the year.
Bill Jelen: Sweet.
Devin Rockford: And it goes Q4-11 and that can be easily.
Oh, I’m putting a function, can be easily put across you display.
Bill Jelen: Okay that's a great formula.
Now, show me that last little trick that you did there -- you had that first formula and rather than reaching for the mouse and grabbing the fill handle and dragging across.
Devin Rockford: Mhm.
Bill Jelen: You filled.
How did you fill?
Devin Rockford: I filled using Ctrl-Right.
So all you need to do is you can go Ctrl-Over to hit the contiguous range, right.
Go to the end of the contiguous range, go one down, highlight everything, and go Ctrl-R.
Bill Jelen: Ctrl-R to copy across, that's cool.
All right so Devin, you're here for the ModelOff finals.
Devin Rockford: Yes.
Bill Jelen: How did you hear about the competition?
Devin Rockford: I heard about it through one of my analysts.
Actually he is a little bit ****y and challenged everybody to this and said that he could beat everybody so I said, anybody who can beat me would get 20 bucks, and nobody is here today so.
Bill Jelen: You're the winner Devin Rockford: Yeah, nobody got 20 bucks from me.
Bill Jelen: So 2,000 people entered and you're one of the final 16 that are here.
I wish you a lot of luck tomorrow.
And for everyone else watching hopefully next year, 2013 you can enter and maybe over here, in New York City.
Devin Rockford: Thank you.
Bill Jelen: Thanks Devin.
I want to thank everyone for stopping by.
I’ll see you next time for another netcast from MrExcel.
This is great, a whole week where I don’t have to come up with the ideas.
I’m here at the ModelOff World Modeling Championships in New York City.
Now, our next contestant got a wonderful all-expense paid trip to New York from, New York.
Yeah here's Devin Rockford with Dates to Quarters, all right Devin.
Devin Rockford: Hi guys I'm Devin Rockford, I work with Ernst & Young in the transaction advisory services and one big problem that I always have is displaying quarters, quarters and years in Excel.
Typically we get stuff formatted with dates and this is a quick easy solution to take care of that.
So the first thing you do is you get your "Q".
You open parenthesis, you close parentheses, Q it displays as a Q -- and then you can do a ROUNDUP function, and Q open parenthesis.
And now you want to reference your month, so you go to a month; reference your date, reference date, close that.
="Q"&roundup(month(B2)).
Bill Jelen: Okay now, I'm going to interrupt -- so F2 there you, had to hit F2 because you were not in Point Mode right?
Devin Rockford: Exactly.
Bill Jelen: And so, when you hit the up arrow it wasn't moving up.
F2 is a great way to go back into that cool point with the mouse.
All right, go ahead.
Devin Rockford: Exactly.
And then you divide that by 3.
So now what you're doing is for instance, in this one it takes 12 divided by 3; which innately enough equals 4.
But if it was 11, it would round up from; it would round up from 3.66667 to 4.
Bill Jelen: We have to tell how many digits there?
You have to tell the number of digits.
Devin Rockford: Oh yeah sorry, you do need to tell the number of digits.
Bill Jelen: Comma- zero (,0).
There we go.
Devin Rockford: Comma- zero (,0).
Bill Jelen: Q4, that’s hot.
Devin Rockford: And that shows Q4.
Bill Jelen: Okay.
Devin Rockford: The next part is very, very easy.
If you want to put a little, a little hyphen in there you just go, and open parentheses; there you have your dash.
And then, all you need to do then is reference a year, and all you have to do is a text function -- go back into enter mode, reference the date again and YY which will format it as about two numbers of the year.
Bill Jelen: Sweet.
Devin Rockford: And it goes Q4-11 and that can be easily.
Oh, I’m putting a function, can be easily put across you display.
Bill Jelen: Okay that's a great formula.
Now, show me that last little trick that you did there -- you had that first formula and rather than reaching for the mouse and grabbing the fill handle and dragging across.
Devin Rockford: Mhm.
Bill Jelen: You filled.
How did you fill?
Devin Rockford: I filled using Ctrl-Right.
So all you need to do is you can go Ctrl-Over to hit the contiguous range, right.
Go to the end of the contiguous range, go one down, highlight everything, and go Ctrl-R.
Bill Jelen: Ctrl-R to copy across, that's cool.
All right so Devin, you're here for the ModelOff finals.
Devin Rockford: Yes.
Bill Jelen: How did you hear about the competition?
Devin Rockford: I heard about it through one of my analysts.
Actually he is a little bit ****y and challenged everybody to this and said that he could beat everybody so I said, anybody who can beat me would get 20 bucks, and nobody is here today so.
Bill Jelen: You're the winner Devin Rockford: Yeah, nobody got 20 bucks from me.
Bill Jelen: So 2,000 people entered and you're one of the final 16 that are here.
I wish you a lot of luck tomorrow.
And for everyone else watching hopefully next year, 2013 you can enter and maybe over here, in New York City.
Devin Rockford: Thank you.
Bill Jelen: Thanks Devin.
I want to thank everyone for stopping by.
I’ll see you next time for another netcast from MrExcel.
