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

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