Skip to main content

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.


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

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



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

^^^ Smarter solution than mine.


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


Thank you both and got it to work using this!

Reply


Terms & Conditions