How to Convert Date to Quarter in Excel

Converting dates to quarters in Excel has several benefits. For instance, it can come in handy while dealing with financial or sales data, enabling you to analyze performance quarterly.

This analysis can help identify trends, track progress, and compare performance across different quarters.

This tutorial shows three techniques for converting dates to quarters in Excel.

About Quarters in a Year

Quarters are three-month periods within a year.

There are two types of quarters:

• Calendar Quarters
• Fiscal or Accounting Quarters

Calendar quarters correspond to the standard calendar year, where the first quarter always begins on January 1 and the fourth quarter ends on December 31, as shown below:

• Quarter 1: January 1 – March 31
• Quarter 2: April 1 – June 30
• Quarter 3: July 1 – September 30
• Quarter 4: October 1 – December 31

Organizations often align their fiscal or accounting quarters with their fiscal year, which may not necessarily correspond with the calendar year.

For example, a company’s fiscal year may begin on October 1 and end on September 30, with the first quarter starting on October 1 and the fourth quarter ending on September 30, as shown below:

• Quarter 1: October 1 – December 31
• Quarter 2: January 1 – March 31
• Quarter 3: April 1 – June 30
• Quarter 4: July 1 – September 30

We will first show how to convert dates to calendar quarters and then describe how to convert dates to fiscal or accounting quarters.

Also read: Convert Month Name to Number in Excel

Method #1: Using ROUNDUP and MONTH Functions to Convert Dates to Calendar Quarters in Excel

We can use a formula combining ROUNDUP and MONTH functions to convert dates to calendar quarters in Excel.

Let’s consider the following dataset showing an organization’s various dates of sales:

We want to use a formula combining the ROUNDUP and MONTH functions to convert the dates into calendar quarters in column B.

We use the below steps:

1. Select cell B2 and type in the below formula:

=ROUNDUP(MONTH(A2)/3,0)

2. Drag or double-click the fill handle in cell B2 to copy the formula down the column.

The formula converts the dates into calendar quarters displayed in column B.

Explanation of the Formula

=ROUNDUP(MONTH(A2)/3,0)

This formula converts a date to a calendar quarter by dividing the month by 3 and rounding up the result to the nearest whole number.

Let’s break down the formula:

1. MONTH(A2): The MONTH function extracts the month component from the date in cell A2. In this case, cell A2 contains the date “2023-01-15,” therefore, the function returns 1 because January is the first month.

2. MONTH(A2)/3: This part of the formula divides the month value by 3 to determine the fraction of the year represented by the given month. Since each quarter consists of three months, dividing the month by 3 gives a decimal value indicating which fraction of the year the date belongs to. In this instance, the month is 1 (January), so the result is 1/3 = 0.333.

3. ROUNDUP(MONTH(A2)/3,0): The ROUNDUP function rounds the value 0.3333 obtained in the previous step up to 1, the nearest whole number indicating that the date falls in the first calendar quarter. The second argument, 0, specifies that we want to round up to the nearest integer.

Ensure your dates are formatted correctly in Excel for this formula to work. If the date is formatted as text or in an unrecognized date format, the formula will return an error.

You can adapt this method to categorize dates into halves or thirds of a year. For halves, replace 3 with 6 in the formula, and for thirds, replace 3 with 4.

Also read: Using IF Function with Dates in Excel

Method #2: Using QUOTIENT, MOD, and MONTH Functions to Convert Dates to Fiscal Quarters in Excel

To convert dates to fiscal or accounting quarters in Excel, we can utilize a formula that combines the QUOTIENT, MOD, and MONTH functions.

Assume we have the following list of dates of sales of a particular organization whose fiscal year starts October 1:

We want to apply a formula that combines the QUOTIENT, MOD, and MONTH functions to convert the dates into fiscal quarters in column B.

We use the following steps:

1. Select cell B2 and type in the following formula:

=QUOTIENT(MOD(MONTH(A2)-10,12),3)+1

2. Double-click or drag the fill handle feature in cell B2 to copy the formula down the column.

The formula converts the sales dates into fiscal quarters in column B.

Explanation of the Formula

=QUOTIENT(MOD(MONTH(A2)-10,12),3)+1

The formula works as follows:

1. MONTH(A2) retrieves the month from the date in cell A2. In this instance, cell A2 contains the date “2023-01-15,” therefore, the function returns 1 because January is the first month.

2. MONTH(A2)-10 subtracts 10 from the month number to align the fiscal year with the calendar year. In this case, the month is January (1), subtracting 10 results in -9, shifting the fiscal year by 10 months.
3. MOD(MONTH(A2)-10,12) calculates the remainder after dividing the adjusted month number by 12. This step is necessary to wrap around the negative values and keep the result within the range of 0 to 11. For instance, in this case, the adjusted month is -9, taking the modulus by 12 results in 3.

4. QUOTIENT(MOD(MONTH(A2)-10,12),3) divides the adjusted month number (after taking modulus) by 3 and returns the quotient. This division groups the months into quarters. In this case, the adjusted month is 3, so dividing it by 3 yields 1.

5. Finally, QUOTIENT(MOD(MONTH(A2)-10,12),3)+1 adds 1 to the quotient obtained in the previous step. This adjustment is necessary because fiscal quarters are usually numbered from 1 to 4. Adding 1 to the quotient shifts the range of quarters from 0-3 to 1-4.

This technique is effective and efficient, but it is complicated.

If you are uncomfortable with it, you can utilize the following alternative, more straightforward method.

Here are a few things to keep in mind when using this method:

1. Date Formatting: This formula depends on Excel recognizing your data as a date. If Excel interprets your dates as text or in an unrecognized date format, the formula will return an error.
2. Fiscal Year Start: The formula assumes that the fiscal year starts in October (10th month). If the fiscal year for your data starts in a different month, you need to adjust the ‘-10’ in the formula to align with the start of your fiscal year. For instance, if your fiscal year starts in April, replace ‘-10’ with ‘-4’.
3. Negative Results: Be careful with negative results from the MONTH(A2)-10 operation. The MOD function handles these correctly, but negative numbers can cause confusion when troubleshooting the formula.

Also read: How to Convert Date to Month and Year in Excel

Method #3: Using CHOOSE and MONTH Functions to Convert Dates to Fiscal Quarters in Excel

To convert dates to fiscal or accounting quarters in Excel, we can utilize a formula that combines the CHOOSE and MONTH functions.

Suppose we have the following list of dates of sales of a particular organization whose fiscal year starts October 1:

To convert the dates into fiscal quarters in column D, use the steps below:

1. Create a reference table like the one below, with months in the first row and the associated fiscal quarter numbers in the second row. Since the fiscal quarter starts from October onwards, October, November and December have been assigned the value 1, and so on.

This table will come in handy as you create the required formula in the next step. Note that we would not be using this table in the formula itself, but it is still a good idea to create this to make sure the values you use in the formulas are correct

2. Select cell B2 on the dataset of dates of sales and type in the following formula as you refer to the table created in the previous step:

=CHOOSE(MONTH(A2),2,2,2,3,3,3,4,4,4,1,1,1)

4. Drag or double-click the fill handle feature in cell B2 to copy the formula down the column.

Explanation of the Formula

=CHOOSE(MONTH(A2),2,2,2,3,3,3,4,4,4,1,1,1)

Here’s the breakdown of the formula:

1. MONTH(A2) extracts the month value from the date in cell A2. It returns a number between 1 and 12, representing the month of the date.
2. CHOOSE() selects a value from a list based on a given index number. In this case, the index number is the month value returned by the MONTH function.
3. The values (2, 2, 2, 3, 3, 3, 4, 4, 4, 1, 1 1) make up the selection from which the CHOOSE function will draw.

Each value corresponds to a specific month. For instance, the first three values (2, 2, 2) represent January, February, and March.

The following three values (3, 3, 3) correspond to April, May, June, etc.

This tutorial showed three formulas to convert dates to quarters in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Group by Quarters in Excel Pivot Table?
• Add Days To Date in Excel
• How to Round Time to the Nearest Quarter Hour in Excel?
• How to Convert Date to Day of Week in Excel?
• Convert Decimal To Fraction In Excel
• How to Convert Date to Serial Number in Excel?
• Convert Number of Days to Weeks in Excel
• Convert Days to Months in Excel