The following formula may be used to calculate the quarter number, e.g., 1, 2, 3, or 4, for a given date in a regular calendar year. This formula uses a combination of the ROUNDUP and MONTH functions. The expected results of the formula are January through March are Q1, April through June are Q2, July through September are Q3, and October through December are Q4.
In the results, Column B titled Quarter (#) is produced using the following formula.
=ROUNDUP(MONTH($A2)/3,0)This formula uses the MONTH function to obtain the month number (1 through 12) for the given date and divides it by 3. As an example, March (month 3) divided by 3 produces the result 1 which is the correct quarter number. January (month 1) divided by 3 produces the result 0.33 which is not the correct quarter number. The ROUNDUP function rounds fractional numbers to the next highest whole number. Using January again, the 0.33 is rounded up to 1 to produce the correct result. The formula divides by 3 because there are 3 months in every quarter.
Also in the results, Column C titled Quarter (Q#), is produced using the following formula which adds a “Q” prefix to the quarter number.
=CONCATENATE("Q",ROUNDUP(MONTH($A2)/3,0))The next image provides the formula view for this entire example.
