Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Nested IF statements between dates

816 1
cancel
Showing results for 
Search instead for 
Did you mean: 
KVC
5 - Automation Enthusiast
5 - Automation Enthusiast

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 2

Hi @KVC - try this:

Screenshot 2019-11-02 at 14.41.39.png

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.