Are you looking for a way to extract the quarter from a date in Google Sheets? Look no further! I’ll walk you through the different formula options to make this happen.
Non-Array Formula to Return Quarter from a Date in Google Sheets
Let’s say you have a date, like June 26th, 2019, and you want to determine which quarter it falls into. Using a simple formula, you can extract the corresponding quarter. Assuming the date is in cell C2, here’s the formula:
=ROUNDUP(MONTH(C2)/3,0)
If you want to add the string “Quarter” or “Q” to the output, you can use the following formula:
="Quarter "&ROUNDUP(MONTH(C2)/3,0)
For example, to return “Q2” instead of “Quarter 2,” just change “Quarter” to “Q” in the formula.
Array Formula to Return Quarter from a Date Range in Google Sheets
If you have a range of dates in cells C2:C5 and you want to extract the quarters for each date, you can use an array formula. Simply copy the quarter formula from cell D2 to the range D3:D5.
Alternatively, you can convert the formula to an array formula using the following syntax:
=ArrayFormula("Quarter "&ROUNDUP(MONTH(C2:C5)/3,0))
You can also use an infinite range by modifying the formula as follows:
=ArrayFormula(if(C2:C<>"","Quarter "&ROUNDUP(MONTH(C2:C)/3,0),))
Date Range to Quarter Using Query Quarter Function
In addition to array formulas, you can also use the Query function in Google Sheets to extract quarters from a date range. Instead of using an array formula, you can simplify the process with the following Query formula:
=QUERY(C2:C,"SELECT QUARTER(C) LABEL QUARTER(C)''")
This formula will give you the same results as the previous array formula. It’s a great option if you want to summarize your data by quarters.
That’s all there is to it! Now you know how to extract quarters from dates in Google Sheets. Enjoy!
