Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 19, 2022 01:11 PM
Need help seeing why the Quarter is not pulling correctly.
I have the following formula trying to pull the Quarter, but it shows Q1 for all the months. Example 12 is showing Q1 when it should show Q4.
IF(OR(FIND(01,{Month}),FIND(02,{Month}),FIND(03,{Month})),“Q1”,
IF(OR(FIND(04,{Month}),FIND(05,{Month}),FIND(06,{Month})),“Q2”,
IF(OR(FIND(07,{Month}),FIND(08,{Month}),FIND(09,{Month})),“Q3”,
IF(OR(FIND(10,{Month}),FIND(11,{Month}),FIND(12,{Month})),“Q4”))))
Here is a picture of the column I am pulling from and the results which you can see the 12 are not correct as they should say Q4.
Jan 19, 2022 05:30 PM
Im not sure, but it’s possible that it’s because 1 is part of the number 12.
Instead of using the FIND function, try using = instead.
For example:
IF(OR(Month=“01”,Month=“02”,Month=“03”),“Q1”)
Jan 19, 2022 05:48 PM
That’s the issue. Each month numbers in the original formula should be wrapped in quotes.
The formula could also be rewritten as:
IF({Month}, "Q" & DATETIME_FORMAT(DATETIME_PARSE({Month}, "MM"), "Q"))
^ I suspect the formula can be even simpler. If {Month} is pulling from a date field, {Quarter} could do the same:
IF({Date}, "Q" & DATETIME_FORMAT({Date}, "Q"))
Jan 19, 2022 09:54 PM
^^^ Smarter solution than mine.
Jan 20, 2022 05:19 AM
Thank you both and got it to work using this!