How to Calculate the Current and Previous Quarter in Google Sheets

This guide will explain how to calculate the current and previous quarter in Google Sheets.

Since financial data is often divided into quarters, this can be helpful for making sales reports and other reports that rely on the fiscal year.

In accounting and financial reporting, companies and governments use the fiscal year as the basis of their reports. This 12-month period is often divided into four quarters, each lasting three months.

Fiscal years are named using the year that the last quarter ends. For example, the fiscal year that runs from April 1st, 2021, to March 31st, 2022, is known as the FY 2022.

Since quarters do not follow the traditional year, it might be difficult to figure out what quarter a particular date falls under. This can be troubling if you create reports requiring you to sort and filter data quarterly.

Let’s take a look at a simple example of a report that will require quarterly data.

Suppose you are making a sales report for your company. Given daily sales data from the past year, you want to determine which quarter the company performed best. You would also like to know the current and previous quarters’ growth.

How can we figure this out in Google Sheets?

If your company follows the calendar year, we can use the QUERY function to retrieve the quarter easily. The QUARTER() function is a built-in method that you can use within a Google Sheets query. Using this method, Q1 will fall from January to March, Q2 from April to June, and so on.

If your company follows a non-standard calendar year, then we can use the CHOOSE and MONTH functions to work out the quarter of a given date.

Let’s learn how to use these functions ourselves in Google Sheets and later test out these methods with actual date values.

A Real Example of Calculating the Current and Previous Quarter

Let’s look at a real example of a spreadsheet that uses a formula to calculate current and previous quarters in Google Sheets.

The example below shows a sales table showing the total amount of cash earned on a particular date. Using the QUERY function, we could get the corresponding quarter of each date. This column was then used in range E1:F5 for a quarterly summation table.

To get the values in Column C, we just need to use the following formula:

=QUERY(A2:A16, "SELECT QUARTER(A) LABEL QUARTER(A) 'Quarter'")

If we want to get the quarter based on a non-standard fiscal year, we can use an alternate formula instead. For example, to get the quarter in the fiscal year ending in March 2022, we can use this formula:

=CHOOSE( MONTH (A5) , 4,4,4,1,1,1,2,2,2,3,3,3 )

The CHOOSE function allows us to hard-code a sequence of numbers that maps months to quarters. In the formula above, the result of the MONTH function will determine which of the next 12 arguments is chosen.

For example, if the date lands in July, MONTH will return the value 7. Using the sequence (4,4,4,1,1,1,2,2,2,3,3,3), we can determine that the date falls on the second quarter.

You can make your own copy of the spreadsheet above using the link attached below. 

If you want to find the current and previous quarter in Google Sheets, follow the guide in the next section to learn more.

How to Calculate Current and Previous Quarter in Google Sheets 

This section will guide you through each step needed to calculate the quarter of a given date in Google Sheets. We will show how you can get the quarter based on either the traditional calendar year or a non-standard calendar year.

Follow these steps to find the quarter of a date based on the calendar year:

1. First, given financial data with a date, let’s use the QUERY function to determine the quarter. Add a new column that will hold our query output.
   
   
2. Next, add the QUERY function we’ve shown in the previous section and add it to the header cell of our new column. In this example, we’ve placed our formula in cell C1. We’ve selected the range A2:A16 as our first argument. This should give us the quarters of each date in our table.
   
   
3. Afterwards, let’s use the same technique to find the current quarter. Add the current date somewhere in your sheet. You can retrieve the current date using the TODAY() function.
   
4. To get the current quarter, simply use the same formula, but adjust it so that we no longer have a label in our output. We can accomplish this by setting the label to an empty string ‘’. In this example, we’ve determined that we’re currently in Q3 of the current calendar year. We can subtract 1 to get the previous quarter.
   
   

If your use case requires a non-standard fiscal year, we can opt to use the CHOOSE and MONTH functions instead to determine the fiscal year.

1. Instead of using the QUERY function, add our CHOOSE and MONTH formula into the financial table. The argument for the MONTH function should be a reference to the current row’s date.
   
   
2. Use the Fill Handle tool to find the quarters of the rest of your dataset.
   
   
3. Similarly, we can use the same formula to get the current quarter and previous quarter.
   

That’s all you need to remember to start using the QUERY, CHOOSE, and MONTH functions to determine the quarter of a date in Google Sheets. In conclusion, this step-by-step guide shows how easy it is to look up the quarter following both the standard and non-standard calendar year.

Calculating the quarter of a given date is just one of many ways Google Sheets can help interpret your data. You can surely find one that suits your use case since there are so many other Google Sheets functions available.

Are you interested in learning more about what Google Sheets can do? 

Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe