Quarter Dates in Excel

Excel is a combination of numeric values and alphabets. The user's project consists of the date depending upon their accounting and financial analysis. Sometimes there is a need to find the date and year present in the data belonging to which quarter. The formula method is used to find the quarter of the given date where the default function is not present in Excel.

How to find the quarter of the given date in Excel?

To find the quarter of the given date, the months are classified based on users' preferences as follows,

Quarter 1- Jan, Feb, Mar

Quarter 2-April, May, June

Quarter 3- July, Aug, Sep

Quarter 4- Oct, Nov, Dec

Step 1: Enter the required date in the column range A1:A10.

Step 2: Select a new cell, B1, and enter the formula as =ROUNDUP (MONTH (Cell name)/ 3, 0). Here A1 is mentioned as the cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

From the above formula, the month function retrieves the month from the given data and divides it with the number 3. The ROUNDUP function is used to round the value to the nearest integer. Finally, the value obtained displays the date present in which quarter. The above worksheet shows the result in column B1:B10, which consists of which quarter the date is present.

Example 1: Suppose the user displays the predefined quarter based on the project. How to calculate it?

Here in this example, the user displays the quarter as follows,

Quarter 1- April, May, and June

Quarter 2- July, Aug, Sep

Quarter 3- Oct, Nov, Dec

Quarter 4- Jan, Feb, Mar

The formula is modified as follows,

=IF(ROUNDUP(MONTH(A1)/3,0)-1=0,4,ROUNDUP(MONTH(A1)/3,0)-1).

Let’s implement this formula in the data as follows,

Step 1: Enter the required date in the column range A1:A10.

Step 2: Select a new cell namely B1 and enter the formula as =IF(ROUNDUP(MONTH(A1)/3,0)-1=0,4,ROUNDUP(MONTH(A1)/3,0)-1).Here A1 is mentioned as cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

The above worksheet shows the result in column B1:B10, which consists of which quarter the date is present. Here ROUNDUP function correctly displays the quarter value for all except the 4^th quarter. It gives the result 0 for the 4^th quarter. To rectify this, the IF function is implemented. If the quarter value is 0, the IF function returns the 4^th quarter.

Example 2: How to display the word quarter in the cell?

Here, the word quarter is displayed in the cell using the formula. The steps to be followed are,

Step 1: Enter the required date in the column range A1:A10.

 Step 2: Select a new cell, namely B1, and enter the formula as =”Quarter”&ROUNDUP (MONTH (A1)/3, 0). Here A1 is mentioned as the cell name.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

The above worksheet's result is displayed in column B1:B10, along with the word quarter. Either the word “q” or “Quarter” is added.

Example 3: How to extract the quarter of the date using CHOOSE function?

Excel provides another way to calculate the quarter of the given date. The CHOOSE function helps to find the quarter of the date. The steps to be followed are,

Step 1: Enter the required date in the column range A1:A10.

Step 2: Select a new cell, B1, and enter the formula as =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4). Here A1 is mentioned as the cell name. The formula works based on first getting the month of the given date, and the index in the formula helps display the required quarter.

Step 3: Press Enter. The result will be displayed in cell B1. Drag the formula toward the required cell range to display the result for the remaining cells.

From the above worksheet, the result is displayed in the column range B1:B10 using the CHOOSE function.

Example 4: How to calculate the quarter of the date for the non-calendar year?

To calculate the quarter for the non-calendar years, the months are classified as follows,

Quarter 1- July, Aug, Sep 

Quarter 2- Oct, Nov, Dec

Quarter 3- Jan, Feb, Mar

Quarter 4- April, May, and June

The steps to be followed to calculate the quarter of the date are as follows,

Step 1:  Enter the required date in the column range A1:A10.

 Step 2: Select a new cell, B1, and enter the formula as =CHOOSE(MONTH(A1),3,3,3,4,4,4,1,1,1,2,2,2). Here A1 is mentioned as the cell name.

Step 3: Press Enter. Drag the formula toward the required cell range to display the result for the remaining cells. The result will be displayed in cell B1.

From the above worksheet, the result is displayed in the column range B1:B10 using the CHOOSE function where the order of index is changed based on the data.

Summary

Multiple projects contain various dates. Hence it is necessary to arrange the data based on the date. The formula will vary based on the quarter of the date. The above tutorial explains the various functions and methods used to calculate the quarter of the date.