# Recipe for getting Months and Quarters from dates

This is not a question, but a recipe I’ve often found useful.

If you have data with dates, you may want to summarize it by month or by quarter. For that, start by adding a formula column which calculates the Month or Quarter for your date.

• For a Month column, a useful formula is:

``````\$MyDate.strftime("%Y-%m")
``````

which produces values like `2021-08`.

• For a Quarter column, here is the magic invocation:

``````"%s Q%s" % (\$MyDate.year, CEILING(\$MyDate.month, 3) // 3)
``````

which produces values like `2021 Q3`.

Once you have a column like this, you can use it as a Group By column with Summary Tables to get a row per month or a row per quarter.

6 Likes

Thank you for sharing that formula!

For all copy-cats: the full code is:
`\$MyDate.strftime("%s Q%s" % (\$MyDate.year, CEILING(\$MyDate.month, 3) / 3))`

TLDR: just this is sufficient to get year+quarter:

``````"%s Q%s" % (\$MyDate.year, CEILING(\$MyDate.month, 3) // 3)
``````

Let me break it down.

The `format % (A, B, ...)` syntax in Python is a formatting construct. In particular, it replaces occurrences of `%s` in the format string with the next argument in parentheses (first one with `A`, next one with `B`). In particular, it replaces the first one with the current year, and the second one with the number of the current quarter.

So if `\$MyDate` were 2021-08-31, then

• `\$MyDate.year` is 2021
• `\$MyDate.month` is 8
• `CEILING(8, 3) / 3` is 3
• `"%s Q%s" % (2021, 3)` is “2021 Q3”.

@Haidosu’s code adds a `\$MyDate.strftime(...)` around the returned value. Now, `strftime` is the method to “format a date/time as a string”. Its argument is a formatting string, in which (similarly to the `%` syntax above), it replaces the occurrences of various placeholders with parts of the date it’s called on: e.g. `%Y` with the date’s year, `%m` with the month, and so on (see the table here for full details: https://strftime.org/). Anything between these placeholders is retained untouched.

When you call `\$MyDates.strftime("2021 Q3")` (which is what happens in this example), it tries to interpret `"2021 Q3"` as a formatting string. It doesn’t have any occurrences of `%Y`, `%m`, etc – no placeholders at all. So the entire string is returned untouched. So it does no harm, but no benefit either.

What would be nice is if you could let `strftime` take care of the formatting, e.g. `\$MyDate.strftime("%Y Q%q")`. Alas, there is no formatting code for quarters. That’s why, unlike for most date formatting needs, to format quarters, we need to calculate the quarter ourselves, and stitch it together into a string without the help of `strftime`.

2 Likes