Nested IF statements between dates

[KVC]

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!

[JonathanBowen]

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]

JonathanBowen:

I’ve created a helper field “year of date” which you can hide if you don’t want to see.

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