How to make a nested IF statement evaluate ALL nested statements

[Cole_Wedemeier]

Hello!

I have a formula with nested IF statements that works except that what I realize I actually need is to not have the logic stop when it finds 1 of the IF statements true, and actually continue on to validate the remaining IF statements.

It very well could be that IF statements are not the right way to reach my goal, so I’ll also explain what my goal is.

We have several ways that our participants in a course interact with us. These include:

• Critiques of work
• Q’s for Coaching submitted
  *** Last Lesson in Course**
• Community Engagement
• Milestone Reached

Each of these has an Airtable field with the most recent date they’ve interacted with us in the respective category.

What I want to do is create a formula that evaluates each of the 5 dates and populates the most recent of the 5. When I’m doing it with nested IF statements currently, if it finds that 1 fields date is greater than the compared fields date, it stops there and populates that date without validating if the others are actually more recent.

If an IF statement is the proper way to do this, but I’m missing another logical argument to be included for this to work, please let me know :slightly_smiling_face: OR, even if there’s another type of formula to create that would truly provide the most recent date of the 4 categories, that’d be helpful too.

The below is only evaluating 4, I want to include the 5th, but already realized the issue when evaluating just the 4…so that’s why it’s missing all 5 fields, and only lists out 4 total ({Critique: Last Submitted}, {Q&A: Last Submitted}, {FB: Last Posted}, and {Most Recent Milestone})

IF(
  {Critique: Last Submitted}>{Q&A: Last Submitted}, 
  {Critique: Last Submitted}, 
  
   IF(
    {Q&A: Last Submitted}>{FB: Last Posted}, 
    {Q&A: Last Submitted}, 

     IF({FB: Last Posted}>{Most Recent Milestone},
       {FB: Last Posted}, 
       {Most Recent Milestone}
)))

[augmented]

@Cole_Wedemeier - I see a few possible problems with your formula. If you don’t mind, try these fixes and lets see what they change.

1. There should only be one closing paren after the final ‘X’

'X')

2. So, move that last paren inside the outer DATETIME_PARSE (right before the ‘X’) to close off the MAX

  ),
'X')

3. The last IF inside the MAX (i.e. Lesson Completed) should not have a comma at the end

IF({Lesson Completed}, VALUE(DATETIME_FORMAT({Lesson Completed}, 'X')))

4. The {Milestone Date} is a lookup field and it might need the DATETIME_PARSE treatment

[Cole_Wedemeier]

@augmented I don’t think I followed quite what you were saying with the portion for step 2. I made updates, and it lets me save. The new difference is that if there is only 1 date in the fields we’re comparing then it populates that date. If there are no dates, then it defaults to 1/1/1970 (Ok, I can deal with that), but as soon as there are more than 1 date, I still get the error. I think it’s just that I am not clear on all of the updates you were mentioning.

DATETIME_PARSE(

   MAX(

       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({Critique: Last Submitted}, 'MM/DD/YYYY'), 'X'))),

       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({Q&A: Last Submitted}, 'MM/DD/YYYY'),'X'))),

       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({FB: Last Posted}, 'MM/DD/YYYY'),'X'))),

       IF({Milestone Date}, VALUE(DATETIME_FORMAT(DATETIME_PARSE({Milestone Date}, 'MM/DD/YYYY'),'X'))),

       IF({Lesson Completed}, VALUE(DATETIME_FORMAT({Lesson Completed}, 'X')))


),'X')

[augmented]

Hi Cole. Looks like you got the parens and commas where I’d like them. One other thing I noticed is the DATETIME_PARSE specifier is not correct for your data. I’m not sure if it matters to Airtable or not, but the one you theoretically should use is ‘M/D/YYYY’ since you don’t seem to have leading zeros on your months or days.

I still can’t see why it doesn’t resolve #3 correctly. If I get a free half hour, I’ll try to recreate everything as you have it.

[augmented · Accepted solution]

@Cole_Wedemeier So, I created a smaller, more accurate example of your setup in order to see if I could help any further. I created three date rollups in a table, using the MAX() aggregator. Below is the formula I used.

DATETIME_PARSE(
   MAX(
       IF({dt1}, VALUE(DATETIME_FORMAT({dt1}, 'X'))),
       IF({dt2}, VALUE(DATETIME_FORMAT({dt2}, 'X'))),
       IF({dt3}, VALUE(DATETIME_FORMAT({dt3}, 'X'))) 
),      
'X')

Here is the screen shot of the table I am using.

As you can see, in rows where there is at least one date, the formula works fine on rollup fields. No inner DATETIME_PARSE is even needed.

All that needs to be added is an outer OR() wrapper that checks all the date columns. I wish I could get into your base to see what’s different from my mock-up, but sadly Airtable doesn’t make that easy.

[Cole_Wedemeier]

@augmented Thank you!

I was guessing that there may be a data table that was displaying the date in local vs everyone seeing GMT, etc. I ensured they were all the SAME and that finally stopped producing the error when I applied your latest example :slightly_smiling_face:

I cannot thank you and @Ben.Young for all your assistance in trying to resolve my formula with me.

DATETIME_PARSE(

   MAX(

       IF({Critique: Last Submitted}, VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X'))),

       IF({Q&A: Last Submitted}, VALUE(DATETIME_FORMAT({Q&A: Last Submitted},'X'))),

       IF({FB: Last Posted}, VALUE(DATETIME_FORMAT({FB: Last Posted}, 'X'))),

       IF({Milestone Date}, VALUE(DATETIME_FORMAT({Milestone Date}, 'X'))),

       IF({Lesson Completed}, VALUE(DATETIME_FORMAT({Lesson Completed}, 'X')))

),'X')

I have so much appreciation for the support in this community! Thank you again!