Extract Quarter from a Date in Google Sheets: Formulas

When preparing quarterly summary reports, you might want to extract the quarter from a date in Google Sheets automatically.

In this post, we’ll explore how to extract the quarter from a date, as well as the quarter with the year. If you want to apply this to a range of dates, the best option is using the QUERY function, which has a scalar function called QUARTER. Let’s dive into the examples below.

The Proper Way to Extract Quarter from a Date in Google Sheets

Assume the date is in cell A1. To extract the quarter in the format “Qn” (e.g., “Q1”), you can use the following formula in cell B1 (or any empty cell):

=IFERROR("Q"&ROUNDUP(MONTH(DATEVALUE(A1))/3,0))
  • You can replace "Q" with "Quarter" or remove "Q"& if you prefer the quarter in numeric format.
  • The formula returns an empty cell if A1 contains text (not a date formatted as text), non-date numeric values, or is empty.

The DATEVALUE function ensures the MONTH function correctly interprets the input as a date. Without DATEVALUE, if A1 is empty, MONTH would incorrectly return 12 (since empty cells are treated as 0, which corresponds to 30/12/1899), or it may return other incorrect results for numeric inputs.

The IFERROR function prevents errors from invalid inputs, leaving the cell empty instead.

Apply the Formula to a Range of Dates

To apply this formula to the range A1:A, you can either drag the formula down or convert it to an array formula:

=ArrayFormula(IFERROR("Q"&ROUNDUP(MONTH(DATEVALUE(A1:A))/3,0)))
  • Replace A1:A with the desired date range.
  • Before entering the formula, ensure cells B1:B are empty for the array formula to work properly.
Example of extracting quarters from a date range in Google Sheets

Extract Quarter and Year from a Date in Google Sheets

To extract both the quarter and the year from a date, use this formula:

=IFERROR("Q"&ROUNDUP(MONTH(DATEVALUE(A1))/3,0)&"-"&YEAR(DATEVALUE(A1)))

This returns the result in the format "Qn-year" (e.g., Q1-2024).

  • Replace "Q" with "Quarter" if you prefer.
  • Like before, DATEVALUE and IFERROR ensure the formula handles only valid date inputs.

Array Formula Version:

=ArrayFormula(IFERROR("Q"&ROUNDUP(MONTH(DATEVALUE(A1:A))/3,0)&"-"&YEAR(DATEVALUE(A1:A))))

This applies the formula to the entire column A.

Example of extracting quarters and years combined from a date range in Google Sheets

Using the QUARTER Scalar Function in QUERY

If you want to extract the quarter in numeric format from a date range, the QUERY function provides a straightforward option. You can also extract the year into a second column.

For the date range A1:A, use this formula to extract the quarter:

=QUERY(A1:A, "SELECT QUARTER(Col1) LABEL QUARTER(Col1)''", 0)
Extracting quarters in numeric format using the QUERY function in Google Sheets

To extract both the quarter and the year into two columns:

=QUERY(A1:A, "SELECT QUARTER(Col1), YEAR(Col1) LABEL QUARTER(Col1)'', YEAR(Col1)''", 0)

This avoids complex calculations by leveraging the scalar functions.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

2 COMMENTS

  1. How might I split data across a date range by quarter? Example:

    Data: sold 150 units during the date range from Jan 15 to July 15.
    Query: On average, how many were sold in Q1, Q2, Q3, and Q4?

    Answer: Q1: 50 Q2: 50 Q3: 50 Q4: 0

    • Hi, Roger Brownlie,

      You can either use a Query Formula or a Pivot Table.

      Please search “Formula to Group Dates by Quarter in Google Sheets” within this tutorial to find the relevant tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.