Recipe for getting Months and Quarters from dates

Ask for Help Formulas
1

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
2

Thank you for sharing that formula! :+1:

For all copy-cats: the full code is:
$MyDate.strftime("%s Q%s" % ($MyDate.year, CEILING($MyDate.month, 3) / 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
4

Thank you for your clarification! :+1:

1 Like