Help

Error in Quarter Calculation Formula

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1066 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Daphne_Snell
4 - Data Explorer
4 - Data Explorer

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
Screen Shot 2020-05-06 at 6.30.14 PM

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

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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.

See Solution in Thread

3 Replies 3

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
18 - Pluto
18 - Pluto

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: