Another formula to get quarters from dates
A while back I wrote about multiple ways to get quarters from dates. Even “manual” quarters (starting with any months you like). You can read that post here. But now I wish to show you yet another way to get quarters from dates, this time by using the CHOOSE function which is not given enough attention by Excel users.
This is an easy and great way to get quarter number from a date. Again presuming the date is in cell A1. The formula is
=CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)
The formula provides you with the quarter number, but if you want the result in a Q1 format, you should write
="Q"& CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)
Since you can manually change what each month translates to (which quarter), this is an easily editable and highly functional formula! Hope the formula gives you many great ideas about other things you can do with it. In my opinion, the CHOOSE function is very useful and is underrated by Excel users.
Related Posts
- September 23, 2014
We have a dish where I come from (Slovenia), called Minestrone. ...
Comments 4
Leave a Reply
Excel Unplugged acknowledgements
Donate
Refreshing look of your blog! Look great! 🙂
btw, I like the “easily editable” part of using CHOOSE in your example!
Thanks, it took me quite a while now to get things in order. I was also talking to some guys about starting a new page for quite some time now and in the past three weeks we finally got around to doing it and even though we are still looking for some contributors, you can check it out at http://letstalkexcel.com. You will also see where I got the idea of the design from.
Your solution is great for non-calendar fiscal quarters. Thanks!
Here’s another method I’ve used to get quarters from a date
=”Q” & ROUNDUP(MONTH(A1)/12*4,0)
Excel Regards,
I use this method:
=LOOKUP(MONTH(a1),{1,4,7,10},{1,2,3,4})