Table of Contents
To convert a date to a quarter and year in Excel, you can use the YEAR and MONTH functions in combination with the IF, ROUNDUP, and DATE functions. This will allow you to extract the year and month from a given date and use it to determine which quarter the date belongs to. Once the quarter and year are determined, you can use the DATE function to convert the values to a date format.
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:
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.