If you have dates that you want to convert into a quarter, you may have realized that Excel does not have a function for it. Therefore we will show you an easy formula that you can use in Excel to convert date to quarter. You don’t have to calculate a quarter from date in Excel because there are several ways to do it. We will first show how to extract quarters from months in Excel.
Table of contents
Find quarter from month
For this tutorial, we will use the information below. There are four quarters in a year, and each quarter consists of three months. Let’s find out how to find the quarter from each month if we don’t have the entire date written in an Excel sheet.
- First quarter – January, February, March
- Second quarter – April, May, June
- Third quarter – July, August, September
- Fourth quarter – October, November, December
Let’s suppose that your Excel data looks like the one below. Here we only have the months of the year written in the first column.
In the second column, we have written the number (1-12) for each month, for example, 2 for February and 8 for August.
The third column shows a calculation of the second column (the number of each month) divided by 3. Three stands for the number of months per quarter.
Convert date to quarter in Excel
Now we will use the ROUNDUP function, which is valuable and easy to use. The syntax of the ROUNDUP(number;num_digits) consists of two figures.
- the date or month
- the number digits
In our case, we have written a list of numbers for each month. We want to find the quarter from each month, so we use the third column cell (C2), which is the month divided by 3; followed by a zero (0). The zero means that the number rounds up without decimals. E.g., 1,33 will round up to 2, which is the nearest number counted upwards.
Calculate quarter in Excel with ROUNDUP formula
First, we have explained how to set up your data to find the quarter for each month of the year. Now, you will find that there is no need for an entire data setup.
To find a quarter from each month, apply the formula below.
ROUNDUP(number of the month/3;0)
- First, you write the number of the month. For example 1 for January, 2 for February.
- Divided by 3 (which represent the months per quarter).
- Lastly, you put a 0 in the formula to round up to the nearest number with zero decimal places. There is no need for decimals in quarters because we want a round number.
Find quarter from date with ROUNDUP MONTH formula
We have used a formula to find the quarter from a month, but now we will extract the quarter from the date. Fortunately, you don’t have to calculate a quarter in Excel.
In our case, we have listed different dates format. Therefore, to find the quarter from each date, you need to write one formula. There is no need to write the number of each month. The formula you need to use to find the quarter from a date is shown below.
- This formula uses the ROUNDUP function and the MONTH function to get the month value from each date.
- Then the month number is divided by 3 for the months of each quarter.
- In the end, we have to write a 0 to get a round number without decimals
Convert date to quarter with a formula
If you would like to write Quarter or Q in front of each number, you can do that easily. You can use the same formula as we showed above. The only thing you need to do is to write the text in front of the formula. Remember that you have to put words in quotation marks (“) or Excel will not read it correctly. Then you have to use the symbol called ampersand (&), also known as the and symbol. If you want to get the quarter from a date with text, please apply the below formula.
or this formula
This article showed you how to calculate the quarter from each month of the year with a formula. You have also learned how to convert date to quarter in Excel with a ROUNDUP and MONTH formula. Now you can also write text in front of each quarter to make it look more appealing.