Error in Quarter Calculation Formula

[Daphne_Snell]

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

[ScottWorld]

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”

))))

[kuovonne · Accepted solution]

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.

[ScottWorld]

kuovonne:

You can also use the SWITCH statement:

Nice! Even simpler! :blush: :raised_hands: