Skip to main content

Nested IF statements between dates

  • September 11, 2019
  • 2 replies
  • 20 views
K
Forum|alt.badge.img+5
  • KVC
  • Participating Frequently

Hello! I’m trying to create a nested IF statement that uses a date field about an event {End Date}, to determine which quarter the event was in but I keep getting invalid formula errors. I am pretty well versed in excel formulas, but my knowledge does not seem to transfer to Airtable. Unfortunately, the quarters are weird and run Q1: 8/15-11/14, Q2: 11/15-2/14, Q3: 2/15-5/14, and Q4: 5/15-8/14. Ideally I would like this formula to continue to work for upcoming years without revision. Any ideas are much appreciated!

2 replies

JonathanBowen
Forum|alt.badge.img+18

Hi @KVC - try this:

The Quarter formula is:

IF(
  AND(Date >= DATETIME_PARSE({Year of Date} & '-01-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-03-31', 'YYYY-MM-DD')),
  'Q1',
  IF(
    AND(Date >= DATETIME_PARSE({Year of Date} & '-04-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-06-30', 'YYYY-MM-DD')),
    'Q2',
    IF(
      AND(Date >= DATETIME_PARSE({Year of Date} & '-07-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-09-30', 'YYYY-MM-DD')),
      'Q3',
      IF(
        AND(Date >= DATETIME_PARSE({Year of Date} & '-10-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-12-31', 'YYYY-MM-DD')),
        'Q4'
      )
    )    
  )
)

I’ve created a helper field “year of date” which you can hide if you don’t want to see. The formula above is based on the “normal” calendar quarters so just modify to match your custom quarters.

JB

    Justin_Barrett
    Forum|alt.badge.img+21

    Hi @KVC - try this:

    The Quarter formula is:

    IF(
  AND(Date >= DATETIME_PARSE({Year of Date} & '-01-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-03-31', 'YYYY-MM-DD')),
  'Q1',
  IF(
    AND(Date >= DATETIME_PARSE({Year of Date} & '-04-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-06-30', 'YYYY-MM-DD')),
    'Q2',
    IF(
      AND(Date >= DATETIME_PARSE({Year of Date} & '-07-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-09-30', 'YYYY-MM-DD')),
      'Q3',
      IF(
        AND(Date >= DATETIME_PARSE({Year of Date} & '-10-01', 'YYYY-MM-DD'), DATE <= DATETIME_PARSE({Year of Date} & '-12-31', 'YYYY-MM-DD')),
        'Q4'
      )
    )    
  )
)

    I’ve created a helper field “year of date” which you can hide if you don’t want to see. The formula above is based on the “normal” calendar quarters so just modify to match your custom quarters.

    JB


    …or replace {Year of Date} with YEAR(Date) and the extra field isn’t necessary.

      Terms & ConditionsAccessibility statement