7

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.

  • I'd suggest that you use different columns to express the quarter and the year. That will make it much easier to filter the data later. eg. 2017, Q1, 403 – Racheet Jul 26 '18 at 10:39
8

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).

  • Some of us are still hoping that this established format will surface in the next draft of ISO 8601, which is being split into two parts now, but itʼs a bit unlikely. ISO 8601-2 will include EDTF, which overloads the MM month field with values above 20 for seasons and quarters. – Crissov Jul 31 '18 at 11:14
8

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.

  • I work at Investopedia. I can say that we generally use Quarter references in a relative way, whereas ISO-8601 is generally for absolute reference of a date in the timeline. – New Alexandria Jul 30 '18 at 2:14
3

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.