Excel Formula for Converting Date to Quarter of the Year

Excel does not have a built-in function for calculating the quarters of a year by default. As surprising as it may seem, this is true. If you need to calculate the quarter number for a specific date, you will have to create a formula.



How to Calculate Quarters in Excel?

The image below shows an example of a formula that can be used to calculate the quarter number of the year:

Download an example formula for converting Dates into Quarters in Excel

The secret of this formula lies in performing simple function calculations. The month number is divided by 3 and rounded up to the nearest whole number.

For example, suppose we need the quarter number that includes the month of August. Since August is the eighth month of the year, you need to divide the number 8 by 3. The result is 2.66, but when rounded up to the nearest whole number, we get 3. This determines that August falls in the third quarter of the calendar year.

The ROMAN function converts Arabic numerals to Roman numerals in Excel.

But what if a company has non-standard quarters that differ from the calendar year quarters?

The formula shown in the image above performs the same calculations. The MONTH function is used to extract the month number from the date, and then the CEILING function is applied to round the decimal number up to the nearest whole number.