Excel Unplugged

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


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.

Comments 4

  1. MF says:

    Refreshing look of your blog! Look great! 🙂

    btw, I like the “easily editable” part of using CHOOSE in your example!

    1. 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.

  2. Jeff Koenig says:

    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,

  3. Jason C says:

    I use this method:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.