Transform a date into a Month/Quarter/Year format

Ask for Help
1

@Robert_Petitto @Jeff_Hager @Darren_Murphy - I swear one of you did a video/write-up on how to transform a date into a reporting column.

In my case I want to transform a date such 5/9/2023 into: Month-May Quarter-Q2 Year-2023

I can then report on all things in ‘May’; all things in ‘Q2’ etc.

I currently create a table which I ‘look-up’ against but I would like to calculate this rather than use the table.

Thanks for all pointers

2

Use date math.

  • For Year, it’s just Year(Date)
  • To get the Month name, I use a lookup table. One column with 12 rows that contain the month names. Then I get the Month Index using Month(Date)-1, and then use that in a Single value column taking Month Index from start of my lookup column.
  • To get the quarter, use the Month Index value in an if-then-else column:
    – If Index is greater than 8, then Q4
    – If Index is greater than 5, then Q3
    – If Index is greater than 2, then Q2
    – Else Q1

Others might tell you to use the Format Date plugin, and that’s fine. But I don’t trust that, so I don’t recommend it.

3 Likes
3

You can probably use a formula to get the quarter (assuming quarters start on the 1st of every month).

ceiling(month(date)/3)

5 Likes