Error in Quarter Calculation Formula

I’m using the Displaying quarters in fiscal year formula in one of my tables, however, for some reason, Q4 months are showing up as Q1 in my table. The formula I’m using is below.

Here is a snippet of my table

Here is the formula listed below:

IF(OR(
FIND(01,DATETIME_FORMAT({Date},‘MM’)),
FIND(02,DATETIME_FORMAT({Date},‘MM’)),
FIND(03,DATETIME_FORMAT({Date},‘MM’))),“Q1”,

IF(OR(
FIND(04,DATETIME_FORMAT({Date},‘MM’)),
FIND(05,DATETIME_FORMAT({Date},‘MM’)),
FIND(06,DATETIME_FORMAT({Date},‘MM’))),“Q2”,

IF(OR(
FIND(07,DATETIME_FORMAT({Date},‘MM’)),
FIND(08,DATETIME_FORMAT({Date},‘MM’)),
FIND(09,DATETIME_FORMAT({Date},‘MM’))),“Q3”,

IF(OR(
FIND(10,DATETIME_FORMAT({Date},‘MM’)),
FIND(11,DATETIME_FORMAT({Date},‘MM’)),
FIND(12,DATETIME_FORMAT({Date},‘MM’))),“Q4”))))

I would use the “Month” function to make it a little bit simpler.

This formula would work:

IF(OR(
MONTH(Date)=1,
MONTH(Date)=2,
MONTH(Date)=3
),“Q1”,

IF(OR(
MONTH(Date)=4,
MONTH(Date)=5,
MONTH(Date)=6
),“Q2”,

IF(OR(
MONTH(Date)=7,
MONTH(Date)=8,
MONTH(Date)=9
),“Q3”,

IF(OR(
MONTH(Date)=10,
MONTH(Date)=11,
MONTH(Date)=12
),“Q4”

))))

You can also use the SWITCH statement:

SWITCH(MONTH({Date}),
  1, "Q1",
  2, "Q1",
  3, "Q1",
  4, "Q2",
  5, "Q2",
  6, "Q2",
  7, "Q3",
  8, "Q3",
  9, "Q3",
  10, "Q4",
  11, "Q4",
  12, "Q4"
)

I suspect that your formula is having an error because you are inputting a number in your FIND function, instead of a string, and when Airtable converts the number to a string, you loose the 0. Thus, the formula is looking for 1 instead of 01 and October, November, and December have a 1 in their month numbers.

Nice! Even simpler! :blush::raised_hands:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.