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]

Hi Cole. Your intuition is right that using IF isn’t the best way here. Pretty straightforward answer to this question.

DATETIME_PARSE(
   MAX(
       VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X')),
       VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X')),
       VALUE(DATETIME_FORMAT({FB: Last Posted}, 'X')),
       VALUE(DATETIME_FORMAT({Most Recent Milestone}, 'X'))
   ),
'X')

You would need to add one more line inside the MAX() function for your 5th date. Make sure your commas are correct. Let me know if it works for you.

[Ben_Young1]

Hello again @Cole_Wedemeier!

The function you’re looking for is the LAST_MODIFIED_TIME() function.

The function accepts multiple arguments, but will only return the most recent modification time on any of the given fields.

Here’s an example:

LAST_MODIFIED_TIME(
    {Critique},
    {Q&A},
    {Feedback},
    {Milestone}
)

There might be something I’m missing, so please let me know if I am.
The same goes for if you have any questions!

---

Quick Edit:

Using this method, you don’t need any of the additional dedicated fields for a last modified time (unless you explicitly want to see the modified times for each of the fields.

[augmented]

Hi Ben. I didn’t understand the problem to exist as you described. Your answer will give the most recently modified field time, but not the most recent date contained in those fields. Did I miss something?

[Cole_Wedemeier]

Hi @Ben.Young !

Thanks for your response. I realize I should elaborate more.

The “most recent” dates that are populated for the 5 ways they can interact with us are all roll-up fields set to show MAX (values). This is because we have linked records where they can submit, or we track, more than once to many of these categories.

So if I do a last modified rule, it will error out as the rollup field in this table is not modified in that way.

@augmented You did not miss anything, however, I see where Ben was going with the logic.

If all the fields have a modified date/time associated with them, and assuming each is updated as the activity occurs, that would work. However, the flaw there is not only in the field time being looked at for a most recent date, but also in that often there can be more than 1 type of interaction captured on the same day, but that actually took place at different dates/times. So I really want to be looking at the actual date input of the interaction, not the field being modified.

[Ben_Young1]

Wassup!

Cole_Wedemeier:

What I want to do is create a formula that evaluates each of the 5 dates and populates the most recent of the 5

Cole_Wedemeier:

if it finds that 1 fields date is greater than the compared fields date, it stops there and populates that date

---

If we actually wanted to return the field value, that would be extremely easy to add in as well!

It’s possible that I may have misunderstood it, but nonetheless, I’ll build the formula that returns the value of the most recently edited field as well and post it.

It’ll probably benefit someone in the future going through the forums looking for an answer.

Be right back!

[augmented]

Got it. All my formula does is convert each date/time to seconds from the Unix epoch, find the maximum seconds, then transform it back into a date format.

[Ben_Young1]

Cole_Wedemeier:

This is because we have linked records where they can submit, or we track, more than once to many of these categories.

So if I do a last modified rule, it will error out as the rollup field in this table is not modified in that way.

Is there anything about your use case that prevents you from actually going to the original table, using the formula I wrote, and then using your rollup or lookup field to look at that field on the record?

[Cole_Wedemeier]

So when I amend your formula, and put it in, I get #error in all the fields. I’m guessing this could be because some dates may be blank for any one of the 5 options?

DATETIME_PARSE(
   MAX(
       VALUE(DATETIME_FORMAT({Critique: Last Submitted}, 'X')),
       VALUE(DATETIME_FORMAT({Q&A: Last Submitted}, 'X')),
       VALUE(DATETIME_FORMAT({FB: Last Posted}, 'X')),
       VALUE(DATETIME_FORMAT({Most Recent Milestone}, 'X')),
       VALUE(DATETIME_FORMAT({Lesson Completed}, 'X'))
   ),
'X')

[Cole_Wedemeier]

So, to be sure I understand… You’re asking if there’s a reason I couldn’t put a “last updated” field in the other tables and then doing the rollup of the last updated field on that table.

I think I essentially do something very similar in that I’m just doing the rollup date on the date of activity field (so not the last updated type, but an actual date field type), am I not?

The tracking of the different interactions happens within each table.

So we have a table for last lesson completed date, linked back to a main student table. Similarly, a table for them to submit their Q’s for coaching linked back to the student record of the main student table.