Using last_modified_time in an IF statement

[Jodie_Kolbeek]

Hello,

I am looking for some help. I’m pretty new to Airtable, so I don’t know if what I am looking for is possible. However, I want to create a formula with the below logic.

IF value in column A IS "YES" > 30 minutes THEN "Needs action".

I then want to create an automation that looks at the value in Column B, and if it has the value “Needs action”, it would trigger the automation.

I currently have:

IF(AND(LAST_MODIFIED_TIME(MINUTE(Column A)= > 30)), "Needs Action")

Is such a formula possible in Airtable?

[Justin_Barrett]

Welcome to the community, @Jodie_Kolbeek! :grinning_face_with_big_eyes: There are a few issues with your formula. First off, the logic is out of order. The LAST_MODIFIED_TIME() function needs to be passed a field reference, while the MINUTE() function needs to be passed a datetime object (like the datetime returned by LAST_MODIFIED_TIME()). You also don’t need the AND() function because you’re not dealing with multiple conditions. The only condition that you’re testing is, “Has it been more than 30 minutes since that field value was last changed to ‘YES’?”

Another issue is that the MINUTE() function looks at the minute value of a datetime object. In other words, if a datetime reports that the time is 10:30, MINUTE() will return 30 and will never change. To see what the time difference is between two times—i.e. the time that a field was updated and the current time—you’ll need to use the DATETIME_DIFF() function.

Before I offer a formula that will work, though, I need to ask this: is the {Column A} field a manual-entry field—e.g. single-select, single line text, etc.—or a calculated field—e.g. formula, rollup, lookup, etc.? This is important to know because LAST_MODIFIED_TIME() will only work with manual-entry fields. In other words, if the “YES” result that you’re looking at is coming from another formula field, this won’t work. In that case, this new formula that you want to build will need to look at the same stuff is driving that “YES” value, which is probably a combination of one or more manual-entry fields.

[Jodie_Kolbeek]

Hi @Justin_Barrett,

Thank you for coming back so quickly and explaining the logic!

To answer your questions, Column A is a single select (YES/NO or Stopped/Live). But the value that appears is based on a script that’s running in the background.

It is not a calculated field as such that the outcome of this field is determined based on a formula that looks at existing Airtable data. But it is a calculated field in the sense that there’s a script that populates the field based on criteria set in that script.

Does that still mean that the formula I’m trying to work out won’t work?

[kuovonne]

Justin_Barrett:

You also don’t need the AND() function because you’re not dealing with multiple conditions. The only condition that you’re testing is, “Has it been more than 30 minutes since that field value was last changed to ‘YES’?”

I think you need the AND condition because you need to check the value of the field. Action is needed 30 minutes after the field was changed to ‘YES’. If it was changed to something else, then no action is needed.

IF(
  AND(
    {Column A} = 'YES',
    DATETIME_DIFF(
      NOW(),
      LAST_MODIFIED_TIME({Column A}),
      'minutes'
    ) > 30
  ),
  'Needs action'
)

@Jodie_Kolbeek Note that the value of NOW() can be off by 5 minutes to an hour, depending on whether or not the base is open and the record is in view. So your automation would probably never run exactly 30 minutes after the change.

[Jodie_Kolbeek]

@kuovonne, Thank you! I’ve tested it out and it seems like your formula works!

Now, to add a difficulty level to my request, ideally the formula reference multiple columns (5 to be precise).
Can I nest those by simply using 5 additional AND statements?
Like:

kuovonne:

IF(
  AND(
    {Column A} = 'YES',
    DATETIME_DIFF(
      NOW(),
      LAST_MODIFIED_TIME({Column A}),
      'minutes'
    ) > 30
  ),

AND(
{Column B} = ‘YES’,
DATETIME_DIFF(
NOW(),
LAST_MODIFIED_TIME({Column B}),
‘minutes’
) > 30
),
AND(
{Column C} = ‘YES’,
DATETIME_DIFF(
NOW(),
LAST_MODIFIED_TIME({Column C}),
‘minutes’
) > 30
),

  'Needs action'
)

[Justin_Barrett]

kuovonne:

I think you need the AND condition because you need to check the value of the field.

I think I jumbled my explanation and my thought process (again). The original formula didn’t need to use AND() because there was only one condition being (incorrectly) checked, but you’re correct that AND() would need to be used for the new formula.

Jodie_Kolbeek:

Now, to add a difficulty level to my request, ideally the formula reference multiple columns (5 to be precise).
Can I nest those by simply using 5 additional AND statements?

Sorta-kinda. First off, you didn’t nest the other options correctly. The new logical function should be wrapped around all of them as part of the first argument to the IF() function, like this (roughly):

IF(
    AND(
        AND([test 1 here]),
        AND([test 2 here]),
        AND([test 3 here]),
        AND([test 4 here]),
        AND([test 5 here])
    ), 
    "Needs action"
)

With that said, I think you want to use OR() instead of AND(). If I’m understanding your goal correctly, you want to see “Needs action” if any of those five fields have been switched to “Yes” 30 or more minutes ago. If that’s the case, the formula would be this:

IF(
  OR(
    AND(
      {Column A} = 'YES',
      DATETIME_DIFF(
        NOW(),
        LAST_MODIFIED_TIME({Column A}),
        'minutes'
      ) > 30
    ),
    AND(
      {Column B} = 'YES',
      DATETIME_DIFF(
        NOW(),
        LAST_MODIFIED_TIME({Column B}),
        'minutes'
      ) > 30
    ),
    AND(
      {Column C} = 'YES',
      DATETIME_DIFF(
        NOW(),
        LAST_MODIFIED_TIME({Column C}),
        'minutes'
      ) > 30
    ),
    AND(
      {Column D} = 'YES',
      DATETIME_DIFF(
        NOW(),
        LAST_MODIFIED_TIME({Column D}),
        'minutes'
      ) > 30
    ),
    AND(
      {Column E} = 'YES',
      DATETIME_DIFF(
        NOW(),
        LAST_MODIFIED_TIME({Column E}),
        'minutes'
      ) > 30
    )
  ),
  'Needs action'
)

If I’m misinterpreting your desired logic, and you only want to see “Needs action” when all five of those fields are more than 30 minutes past their last change to “Yes”, then change the OR() to AND().

[Jodie_Kolbeek]

@Justin_Barrett, your assumptions are correct! I’ve added the formula and will have it run for a couple of days to see if it does what I want it to do (reading your and @kuovonne explanation, it should!)

For future reference: I got a ‘formula not accepted error’ because it was missing a ) at the end.

[Justin_Barrett]

Jodie_Kolbeek:

For future reference: I got a ‘formula not accepted error’ because it was missing a ) at the end.

My hunch is that you might have missed that final parenthesis when copying the formula. I double-checked what I entered above, and all the needed parentheses are there.

[Jodie_Kolbeek]

Not unlikely that I missed it in my copy-paste @Justin_Barrett!