Table of Contents
Google Sheets allows users to convert a date to the corresponding quarter and year by using the QUARTER and YEAR functions. The QUARTER function will take the date as an input and return the quarter, while the YEAR function will take the date as an input and return the year. Both functions can be combined to convert a date into the corresponding quarter and year. Additionally, users can use the TEXT function to format the date in a desired format, such as “Q1 2020”.
You can use one of the following formulas to convert a date to a quarter and year in Google Sheets:
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.