Dates To Quarters In Excel

Can Excel return the quarter of the year based on a date? If the date is in the January to March range return 1 (first quarter), in the April to June range return 2, etc.?

Scott L. Schaaf

If computer geeks had invented the calendar, getting the quarter would be a snap. The months would be numbered from 0 to 11, the quarters from 0 to 3. To get the quarter you'd just divide the month by three and toss the remainder.

One way to get the current quarter in our real-world, one-based system is to temporarily step into geek world. Subtract one from the month number, get the zero-based quarter number, and add one. Like this: =(TRUNC((MONTH(A1)-1)/3))+1.

However, I prefer a more elegant solution using the less-known ROUNDUP() function. ROUNDUP rounds a number up (away from zero) to the specified number of decimal places. The function =ROUNDUP(MONTH(A1)/3,0) returns the correct quarter number for the date found in cell A1.

blog comments powered by Disqus