This post is going to show you all the ways you can get the quarter from a date value in Google Sheets.
Getting the quarter from a date value can be crucial for many businesses since results are often summarized by quarter.
Extracting the Quarter from a date is tricky and has no dedicated formula. Different countries follow different fiscal year patterns, and it isn’t easy to develop a function that can work in all cases.
There are various built-in functions to extract the year, month, and days from a given date value in Google Sheets. However, no specified function can return the quarter value.
The good news? Various functions from Google Sheets can produce the quarter values required for any quarterly reporting.
Throughout this article, you will learn various ways of extracting quarter values from a date. Get your copy of the example workbook If you want to explore the various ways you can use to extract the quarter from a date.
You can even use these techniques for non-standard calendar year quarters!
Get the Quarter from a Date with the CHOOSE Function
Quarters are tricky because different countries follow different fiscal year patterns where the fiscal year starts from a different month than January.
For such cases, the combination of the CHOOSE and MONTH function can be used for extracting the quarters.
This combination can be the go-to formula to calculate the quarter values from date due to its generic nature and adaptability with almost every type of fiscal year.
The CHOOSE function works similarly to the SWITCH function but is more simplified.
This function requires an index value to return the value associated with that index.
The MONTH function returns the month value ranging from 1 to 12.
Syntax of the CHOOSE Function
Syntaxes for the CHOOSE and MONTH functions are as follows
= CHOOSE ( index, choice1, [choice2, …] )
index
is a numeric value that decides what choice to be returned. Ex. If the index is 1, choice1 will be picked; the formula will return choice2 if the index is 2, and so on. Up to 30 index values are allowed in this function. It is a mandatory argument.choice1
is a mandatory argument and a potential value to be returned if the index is 1. This value can be of any data type such as text, number, date, formula, or even a cell reference.choice2
is an optional argument that specifies the value to be returned if the index is 2.
Syntax of the MONTH Function
= MONTH ( date )
date
is either a date value or a cell containing the date value out of which you want to extract the month value. It is a required argument.
Formula to Extract the Quarter from a Date
Suppose you have date values in column A as shown above. In column B, you want to return the quarter values such as 1, 2, 3, or 4 based on the date.
You can check out this post to learn how to insert a date value in Google Sheets.
= CHOOSE ( MONTH ( A2 ), 1,1,1,2,2,2,3,3,3,4,4,4 )
The above formula will calculate the quarter value from the date.
The MONTH function extracts the month value between 1 to 12 and uses it as an index argument to the CHOOSE function.
The CHOOSE function then returns the value for that index. For example, the MONTH function will return the index as 6 for the date of 2021-06-26. Then the CHOOSE function returns the sixth value from choice1, choice2, choice3, etc.
Drag or copy and paste the formula down across the cells and you will get the quarter from each date.
Get the Quarter from a Date with the ROUNDUP Function
A little math can do the trick for you.
Another combination of functions uses ROUNDUP and MONTH to calculate and return the quarter value in numbers from a given date value.
Syntax for the ROUNDUP Function
= ROUNDUP ( value, [places] )
value
is a required argument that specifies a numeric value to round up.places
is an optional argument that shows what decimal place the value should be rounded up to. The default value is set to zero if not specified.
= ROUNDUP ( MONTH ( A2 ) / 3, 0 )
The above formula will extract quarters as 1, 2, 3, 4.
Drag the formula down or copy and paste the formula down to get the quarter for each date.
The MONTH function first extracts the month as a number between 1 to 12 based on the date. The month value is divided by 3 since there are 4 quarters in a year which span 3 months each.
The fractional part from the division operation is removed with the ROUNDUP function. This will round the value up to the next integer value since 0 is specified for the places argument.
For example, if your division returned as 0.666667, this function would round it up to 1.
Get the Quarter from a Date with the QUERY Function
The QUERY function allows you to write SQL-like queries in Google Sheets.
This function is an amazing option to use when you want to perform any calculation on your dataset.
Check out the complete guide to the QUERY function in Google Sheets for further details on everything it can do!
The QUERY function itself has the QUARTER clause which you can use to extract the quarter of any date value.
You can utilize this function and generate the numeric quarter values such as 1, 2, 3, and 4.
For example, you might want to get the current quarter based on the current date and time.
=QUERY(A2:A, "SELECT QUARTER(A) LABEL QUARTER(A) 'Quarter'")
Add the above formula to your sheet to get the quarter values.
You don’t need to copy and paste the formula down as the QUERY function works to return an array with the calculated values for each row.
The QUERY function first takes the data range as the entire column of dates.
The SELECT statement gathers all the data from that range with the QUARTER function extracting the numeric quarter values from each date.
The result is a row-by-row set of quarter values. The LABEL clause is then used to label the results as Quarter.
📝 Note: Make sure the column label is enclosed in single quote marks 'Quarter'
.
Get the Quarter from a Date with an Apps Script Custom Function
Apps Scripts are a great way to automate almost anything. It is also a great way to build custom functions in Google Sheets.
Check out the beginner’s guide to Apps Scripts in Google Sheets for more details on how to use this amazing tool.
There is no dedicated function inside Google Sheets to return the quarter from date values. However, with the help of Apps Scripts, you can build one!
To open the Apps Script editor, go to the Extensions menu and select the Apps Script options. This will launch the Apps Scripts editor in a new browser tab.
You can then copy and paste the following code inside the Apps Script editor to create a custom function that extracts the quarter value from the date value.
function QTR(date) {
var numMonth = date.getMonth();
switch (numMonth) {
case 0: return "Q1";
case 1: return "Q1";
case 2: return "Q1";
case 3: return "Q2";
case 4: return "Q2";
case 5: return "Q2";
case 6: return "Q3";
case 7: return "Q3";
case 8: return "Q3";
case 9: return "Q4";
case 10: return "Q4";
case 11: return "Q4";
};
};
Once the code is copied inside the editor, click on the Save button on the ribbon to save the code. You don’t need to run the code as it will run when you use the function in your sheet.
- This defines a function with name
QTR
with an argumentdate
. - The variable
numMonth
is defined to hold the numeric month value that you extract from the date usingdate.getMonth()
method. For example, if your date value is 06/25/2021, this method should return the number 5 as an output. The month values will be anything between 0 to 11 based on the month of the inputdate
. - The
switch (numMonth)
statement returns a value based on thenumMonth
.
= QRT ( A2 )
Your custom function is ready to extract the quarters from a date.
You can use the above formula to find the quarter. The function takes a date as an argument, in this case the date in cell A2.
The function automatically returns a text of Q1, Q2, Q3, or Q4 depending on the date value passed. Make sure to drag this formula down the cells to return the quarters for each cell with a date.
How to Show the Quarters as Q1, Q2, Q3, or Q4
Well, the quarter numbers again do not look so great visually to the naked eyes. However, you can always customize them and align a text of your choice as a prefix to them and change their visual appearance in Google Sheets. Sharing two methods of customizations below:
Show Quarter with Concatenation
Using the concatenation operator or function, you can add a string “Q” before each quarter value you extract through the first two methods discussed in this article. You can use any of the following two formulas to get the result.
= "Q" & CHOOSE ( MONTH ( A2 ), 1,1,1,2,2,2,3,3,3,4,4,4 )
= CONCATENATE ( "Q", CHOOSE ( MONTH ( A2 ), 1,1,1,2,2,2,3,3,3,4,4,4 ) )
Either of these can be used to return the output shown above.
Show Quarter with Custom Number Format
Another way you can show a Q in front of the quarter number is with a custom number format.
This won’t change the number value into a text value like with concatenation.
Follow these steps to create a custom number format.
- Select the cells to which you want to apply the custom format.
- Go to the Format menu.
- Select Number from the options.
- Select Custom number format from the submenu.
This will open up the Custom number formats menu.
- Add
"Q"0
into the Custom number format input. - Press the Apply button to accept
This will now show a Q infront of the quarter number without changing the value.
Get Quarters From a Non Standard Calendar Year
Not all people follow the same Fiscal Year!
In some countries, the Fiscal Year may start from October and last until September of the following year.
Some countries prefer to start their year in July.
It is possible to accomodate these non standard calendar years using the CHOOSE and MONTH functions combination in such cases.
= CHOOSE ( MONTH ( A2 ) , 2,2,2,3,3,3,4,4,4,1,1,1 )
For example, the above formula can be used to accomodate a fiscal year that starts in October.
You can change the position of quarters in the CHOOSE function to create a custom formula of your own that starts capturing the quarters from that month onwards.
Conclusions
You have seen different ways of extracting quarter values from a date.
The combination of CHOOSE or ROUNDUP combined with the MONTH function allows you to extract the quarter as a number from 1 to 4.
The QUERY function has a QUARTER clause that allows you to extract the quarter value directly from a date.
You can even use Apps Script to create a custom function that can extract the quarter from a given date.
With these options it’s possible to display the quater as a number or a text string.
Finally, simple adjustments can be made to the solutions to capture the quarter values from any non-standard calendar year.
Did you already know some of these methods? Do you have any of your own techniques for extracting the quarter from a date? Share it with me in the comments section below!
0 Comments