Is there a standard date format that expresses Quarters?

Question

What date format would you use to express a quarter, that is still recognized as a date format?

For example I have spreadsheet of rows with a date column with dates in YYYY-MM-DD format.

I want to transform the data, grouping by quarter, counting the rows in each quarter.

The output would be something like

Q4-2017, 403
Q1-2018, 298
Q2-2018, 139

But I want to then filter down that list to only this year etc.

What format would you use for the resulting date column that expresses the quarter, while still being a recognized date format?

From my research it seems like you would need to have 2 date columns in order to express a time period. But was wondering if I am missing something.

Answer 1

As already said, there's no standard. However, if there was one, it would look differently...

When using YYYY-MM-DD for dates, then you really should not use QQ-YYYY for quarters. The point for the ISO field ordering is that they're ordered according to their weight: Year is more important than month, and so on.

This way you can sort the dates as string and it's chronologically sorted. That's more than just an implementation detail. The user can easily see that

2018-08-01
2018-03-03
2017-12-12

are descending, while this is rather impossible with other formats.

So your quarters should look like

2017-Q4
2018-Q1
2018-Q2

if you really want to use a single column for them. Using multiple columns was said to be better, but IMHO it depends. For sorting and filtering, one column works fine (text-fragment filters like -18, 1999, or Q4 are easy to use).

Answer 2

When it comes to standards, we can distinguish two types, de jure and de facto. The first type is the one defined by some established authority, whereas the second one is the type that emerges in practice over time, often informally.

While the predominant standard in date and time formats is ISO-8601, currently it does not explicitly specify a format for quarters. Something curiously similar, admittedly little to do with your issue, is the week-of-the-year part of the specification, allowing formats along the lines of 2018-W30. Actually, ISO allows for variations, thus giving the implementation some freedom.

Putting ISO aside, due to its dominant status, it is very probable that any standards you encounter are just local or partial. You can see this in the following explanation in Investopedia:

Not all companies use the uniform quarter standard. For example, Wal-Mart Stores' first quarter is February, March, and April; Apple Inc's Q1 is October, November, and December; Microsoft Corporation's Q1 is July, August, and September; etc. In addition, certain governments use different quarter systems. For example, the first quarter of the United States federal government’s fiscal year is October, November and December, Q2 is January, February and March, Q3 is April, May and June, and Q4 is July, August and September. State governments, also, may have their own fiscal calendars.

Whereas it talks about processes, and not writing formats, it is indicative of the variance you might encounter in your search. At the end, I'd suggest that using an intuitive approach as the one you suggested, or constructing yourself a format along the lines of ISO's week formats (thus 2018-Q2 or 2018Q2), might be your best hope to be aligned to some standard. However, notice @maaartinus' comment demonstrating that the ISO-influenced variation is more workable when it comes to sorting, even when the data is treated as strings.

When I personally had to report aggregated data, I also used two values (one for years and one for quarters), because this allowed for more flexible processing (e.g. filtering) after that. However, as indicated in the answer by @maaartinus, this is easily achievable as an expression, rather than having it stored as separate columns.

Answer 3

As of 2019-02, there exists ISO 8601-2:2019 - Date and time — Representations for information interchange — Part 2: Extensions. This is an optional extension to the ISO 8601-1:2019 standard (refer to Extended Date/Time Format (EDTF) Specification for details).

Section 4.8 of ISO 8601-2 specifies "Sub-year groupings". It defines numbers greater than 12 to place in the "month" part of the extended format only. Various set periods are specified, like 21 for "Spring (independent of location)" and 29 for "Spring - Southern Hemisphere".

Number	Quarter
33	Quarter 1 (3 months in duration)
34	Quarter 2 (3 months in duration)
35	Quarter 3 (3 months in duration)
36	Quarter 4 (3 months in duration)

So, using the current year as an example, 2022-36 represents the quarter in which I am authoring this answer.

I do not have full access to the standard so I am unsure if, for example, 33 is meant to represent XXXX-01-01/XXXX-04-01 or if it is meant to represent the concept of a 3-month period named "Quarter 1" (as 21 represents the concept of Spring no matter when it happens).

This matters because there are many organizations and countries that use a Fiscal Year that does not align with the Gregorian calendar. For example, the US Government's fiscal year 2022 quarter 1 occurred in the Gregorian calendar year 2021 quarter 4.

I am also unsure if this standard is supported by Microsoft Excel, Google Sheets, LibreOffice Calc, or other spreadsheet programs. Probably not as of now.

Answer 4

I have also seen this being used in the industry:

181 = 2018 Q1
182 = 2018 Q2
183 = 2018 Q3
184 = 2018 Q4

This might be something similar to what you're looking for.

Answer 5

You can use the ISO duration or period designation to denote quarters, for example:

2021-01-01/P3M for the first calendar quarter of 2021.

This specifies a period of 3 months starting at the beginning of 2021. This method enables any system of quarters to be defined - calendar year, financial year, and so on, and even mixed but still sort properly.

It is also possible to define quarters by their end rather than start date, for example:

P3M/2021-04-01

or by start and end date:

2021-01-01/2021-04-01

Note that '--' is a permitted replacement for '/', which may be needed if the quarter is to be used in a file name. So

2021-01-01--2021-04-01 is the same as 2021-01-01/2021-04-01,

and

2021-01-01--P3M is the same as 2021-01-01/P3M.

Answer 6

If you are using ISO date formats like CCYY-MM-DD elsewhere anyway, CCYY-QQ is the logical and practical and established and well-understood extension, where CCYY-Q without the distinctive letter would only be logical, too.

Starting with the largest unit of timekeeping, i.e. the year, yields the intuitive order for numeric formats, but in natural languages the most interesting, usually the smallest, unit may come first. That means, Third quarter of 2018, but 2018-Q3.

Be aware though, that there are several different conventions for what a quarter year is and when counting them begins:

• 13-week quarters, once in five to six years one (possibly the final one of the year) has 14 weeks, may be synchronized with ISO week dates (Monday start, W01 is the first that has the majority of its days in January).
• 3-month quarters, intuitively starting with Jan+Feb+Mar, but in many legislations, fiscal years and hence fiscal quarters may and do start on other dates.
• 90-day quarters with some arbitrary handling of the other five to six dates.
• 91-day quarters more or less TBD with one of the other types.

This may be of little interest to you now, but ISO might standardize one type or the other for this notation some time in the future.

PS: As predicted and already put in an answer by @Cliff, ISO did in fact standardize a notation for quarters and seasons – alas, by assigning arbitrary month numbers to them. The development of EDTF, which makes up the most part of the first edition of ISO 8601-2, was lead by the US Library of Congress and is indeed focused on enabling librarians, historians or genealogists to store dates they encounter. That is, it is neither focused on actual usability by humans nor on actual business needs.