Skip to main content
Solved

Error in Quarter Calculation Formula

  • May 6, 2020
  • 3 replies
  • 24 views
D

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

Best answer by kuovonne

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.

    3 replies

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9780 replies
    • May 7, 2020

    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
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • 6009 replies
    • Answer
    • May 7, 2020

    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
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • 9780 replies
    • May 7, 2020

    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:


    Terms & ConditionsAccessibility statement