QUARTER

Transform dates to financial quarter in Google Sheets

Data GenerationCyrus Radfar
Jul 7

The QUARTER function is an essential tool for pivoting and grouping data by quarter.

Function Signature

=QUARTER(input, format, delimiter)

Argument Definitions

[range of dates] input; required - date or range of dates to convert. If string or number is passed, the function attempts to convert to a date.

[number] format - how to generate combinations

  • 0 - Q1'YY - Q4'YY [default]
  • 1 - Q1 - Q4
  • 2 - Number of quarter: 1, 2, 3 or 4
  • 3 - yyyy.Q1 - yyyy.Q4

[string] delimiter - also known as the separator for items in a given combination

Use the Cheat

How to Add a Cheat to a Google Sheet

Click the Code Box to Copy

Cheat copied to clipboard ✂️👍
/** * Create key for financial quarter for a date. * * @param {Date} input - input date * @param {number} format - quarter key type * - 0 - Q1'YY - Q4'YY [default] * - 1 - Q1 - Q4 * - 2 - a number 1 - 4 * - 3 - yyyy.Q1 - yyyy.Q4 * * @param {string} delimiter - separator for return format 3 only * @param {boolean} add_header - true by default, for array input the function returns a header "Quarter" * @return financial quarter of date based on format parameter. * * @customfunction */ function QUARTER(input, format = 0, delimiter = '.' ) { const is_array = input instanceof Array; let ret = [[]]; if (input instanceof Array) { const is_matrix = input[0].length > 1; for (let i=0; i < input.length; i++) { if (is_matrix) { for (let j=0; j < input[i].length; j++) { if (!ret[i]) { ret[i] = []; } ret[i].push(single_quarter_(input[i][j], format, delimiter)); } } else { if (!ret[i]) { ret[i] = []; } ret[i].push(single_quarter_(input[i][0], format, delimiter)); } } } else { ret[0] = [single_quarter_(input, format, delimiter)]; } return ret; }; function single_quarter_(input, format = 0, delimiter = '.') { if (!(input instanceof Date)) { input = new Date(input); } const q = input.getQuarter(); const year = input.getFullYear(); switch(format) { case 1: return "Q" + q; case 2: return q; case 3: return year.toString() + delimiter + "Q" + q; }; // default case let year_str = year.toString(); if (year < 1000) { return "The year is prior to 1000 AD. This looks like an error. If not, email us at support@lassie.ai to tell us what's up." } return "Q" + q + "'" + year_str.substring(year_str.length-2, year_str.length); } Date.prototype.getQuarter = function() { const q = (this.getMonth() / 3) + 1; return Math.floor(q); };
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

We've built an interactive tutorial in Google Sheets where it's easy to see how the function works. You can read the definition, edit examples, and do some practice examples to verify that you understand how to use the function.

Copy TutorialView TutorialDownload AppScript Code
Convert dates to financial quarter for pivoting metrics by quarter.
Cyrus RadfarCyrus Radfar

Cyrus is the friendly full-stack founder of spread_cheats. He's a work optimization fanatic.

He's also working on other projects through V1.

Related Cheats