There’s an old quip that says dyslexic philosophers lay awake at night wondering if there is a dog.
Well I’m not dyslexic but a few things tug at my mind at night. For example: Why don’t more women find me attractive? What’s this stuff on my feet? And why isn’t there a function in Excel that translates a date into a quarter?
That’s right, there is no native function in Excel that converts a date into a quarter. Amazing, is it not?
Here is an easy technique for converting a date into a quarter using the CHOOSE function.
For you newbies, the CHOOSE function returns an answer from a list of choices based on a position number. If you were to type =CHOOSE(2, “Gold”, “Silver”, “Bronze”, “Coupon”) you would get Silver – because ‘Silver’ is the second choice in your list of choices. Replace the 2 with a 4, and you would get ‘Coupon’ – the fourth choice.
The idea here is to use the CHOOSE function to pass a date to a list of quarter numbers.
The formula shown in column B tells Excel to use the Month number for the given date and select a quarter that corresponds to that number. In this case, since the month is June, Excel returns the sixth choice (June is the 6th month). The sixth choice here happens to be a 2. June is indeed in the 2nd quarter.
Now, here is the cool thing about this technique. Suppose you work for a company that refuses to believe that the year starts in January. Let’s say that your company’s fiscal year starts in October. Well you can easily compensate for this by simply adjusting your list of choices to correlate with your fiscal year’s start month.
This formula now says that June falls in the third quarter.
Nice trick! When I saw this, I though there had to be a more compact way, because after all the quarter is just the result of the integer division of the month by 3… so I tried:
=1+INT((MONTH(A1)-1)/3)
Works great, except that you have to then format the cell as a number, because Excel assumes by default that this is a date… Not to mention that your approach is just much clearer in the case of quarters not based off January!
Fantastic! As you say, I have a client who has a business year that is different to the calendar year, so this would be great for them.
Its so simple (but I didn’t think it did I?)
Thanks
What’s best…to have women find you more attractive, or to have more women find you attractive?
If you were to tally up the amounts of any women that did find you attractive (and presumably told you as much, otherwise your data is severely suspect) then you may want to check how your attractiveness is improving (or more likely, not) from year to year.
For a calender year, no problem…just use the ol =year(A1) function. But checking out your pull on a nonfiscal year is much harder.
But I’ll go ahead and give you the formula, while you wash your feet, Mike. I’m good like that.
=CHOOSE(MONTH(A1),YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1))
Whoops, the old word wrap.
=CHOOSE(MONTH(A1),YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,YEAR(A1)-1,
YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1),YEAR(A1))
Whats wrong with using
=ROUNDUP(MONTH(A1)/3,0) . works fine for me
Pete: Your formula does work if Q1 starts in January. It will fail though, if you have a fiscal year where Q1 starts in any other month.
Mike -
When I first saw you present this, I thought it was dumb, but I politely (and uncharacteristicly) kept my mouth shut. Then you explained how it worked so nicely with fiscal years which are offset by an arbitrary number of months from January, and I realized how clever this is, and how smart it is to keep quiet.
Jon: Hey, that’s the same reaction I get from my wife. Although she never keeps quite about my dumb ideas.
Hi Mike
Nice post
See the Daterefiner add-in for a few other formulas
http://www.rondebruin.nl/datarefiner.htm
Ron
Ron: Akismet Plugin stopped your first post. Sorry about that. I hope I fixed the glitch.
Ahh, you have a wife. Then perhaps better that one woman finds you more attractive, than if more women found you attractive once.
One more, if FY stars in October.
=MATCH(INT((MONTH(A1)+2)/3),{4,1,2,3},0)
Regards
Another way, for those of us who still believe years begin in January:
=CEILING(MONTH(A1),3)/3
freakin’ awesome…..honestly you have found a way to write posts that actually make it fun to read!
I have a doubt in this. For the date, 10th dec 2010, when I use the choose month fucntion, I can see the Quarter is displayed as 1. But it should be “4″ right? please assist
Ramesh:
It depends on your organization. In some companies, the fiscal year starts in October.
So for them, Q1 would be October, November and December. So this formula would be correct:
=Choose(Month(A1), 2,2,2,3,3,3,4,4,4,1,1,1)
If you are talking about a calendar year, this formula is more appropriate:
=Choose(Month(A1), 1,1,1,2,2,2,3,3,3,4,4,4)