Table of Contents
Converting a date to the corresponding quarter and year in Google Sheets can be done using the “QUARTER” and “YEAR” functions. The “QUARTER” function extracts the quarter from a given date, while the “YEAR” function extracts the year. These functions can be combined to create a formula that will automatically convert any given date to its corresponding quarter and year. This is a useful tool for organizing and analyzing data in a time-based format.
Google Sheets: Convert Date to Quarter and Year
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.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I convert a date in Google Sheets to the corresponding quarter and year?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-convert-a-date-in-google-sheets-to-the-corresponding-quarter-and-year/
stats writer. "How can I convert a date in Google Sheets to the corresponding quarter and year?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-convert-a-date-in-google-sheets-to-the-corresponding-quarter-and-year/.
stats writer. "How can I convert a date in Google Sheets to the corresponding quarter and year?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-convert-a-date-in-google-sheets-to-the-corresponding-quarter-and-year/.
stats writer (2024) 'How can I convert a date in Google Sheets to the corresponding quarter and year?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-convert-a-date-in-google-sheets-to-the-corresponding-quarter-and-year/.
[1] stats writer, "How can I convert a date in Google Sheets to the corresponding quarter and year?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I convert a date in Google Sheets to the corresponding quarter and year?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.