Excel: How to Convert Date to Quarter and Year


You can use one of the following formulas to convert a date to a quarter and year in Excel:

Formula 1: Convert Date to Quarter Only (e.g. Q1)

="Q" &INT((MONTH(A1)+2)/3)

Formula 2: Convert Date to Quarter and Year (e.g. Q1-2022)

="Q" &INT((MONTH(A1)+2)/3) & "-" & YEAR(A1)

Formula 3: Convert Date to Full Quarter Name and Year (e.g. Quarter 1 2022)

="Quarter " &INT((MONTH(A1)+2)/3) & " " & YEAR(A1)

Note that each formula assumes the date is in cell A1.

The following examples show how to use each formula in practice.

Example 1: Convert Date to Quarter Only

We can use the following formula to convert a date to a quarter only:

="Q" &INT((MONTH(A2)+2)/3)

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

The quarter for each date in column A is shown in column B.

Example 2: Convert Date to Quarter and Year

We can use the following formula to convert a date to a quarter and year:

="Q" &INT((MONTH(A2)+2)/3) & "-" & YEAR(A2)

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Excel convert date to quarter and year

The quarter and year for each date in column A is shown in column B.

Example 3: Convert Date to Full Quarter Name and Year

We can use the following formula to convert a date to a full quarter name and year:

="Quarter " &INT((MONTH(A2)+2)/3) & " " & YEAR(A2)

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

The quarter and year for each date in column A is shown in column B.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Compare Dates Without Time in Excel
How to Use SUMIFS with a Date Range in Excel
How to Filter Dates by Month in Excel

Leave a Reply

Your email address will not be published.