IF, AND Formula to remind people to update records

[Madeline_O_Conn]

Hi all,

I’m looking to create a formula to show a :rotating_light: when a record’s status is “PENDING” and it has been more than 10 days since it was updated. I’ve tried doing this a few ways using both a column with the Last Modified Date field, and “Today” in the formula itself, and then also one where I created a new column that shows the difference between the Last Modified Date Today. Please help!

[W_Vann_Hall]

Try

IF(
    AND(
        {Status}='Pending',
        DATETIME_DIFF(
            TODAY(),
            LAST_MODIFIED_TIME(
                {Status}
                ),
            'days'
            )>10
        ),
    '🚨'
    )

I think that will work. If you get slight anomalous results, it’s probably because of a GMT mis-match, and you may need to tweak the formula a bit; start by replacing TODAY() with NOW(). If you still have the ‘ :rotating_light: ’ alert changing a few hours too early or too late, post again, and we’ll see if anyone can help. (I don’t happen to have a data entry field I know I updated at an easily testable time without having to suss out the LMT of various fields in my ‘scratch’ test base — and I’m feeling lazy. :winking_face: )

[Madeline_O_Conn]

Thanks W! Tried this and was told the formula was invalid. Followed your tip and changes TODAY() to NOW() and got the same thing unfortunately. Any other thoughts? Appreciate your help!

[W_Vann_Hall]

No, that’s definitely a valid formula – syntactically, that is. The only two things I can see possibly wrong would be (1) a typo, if you didn’t cut-and-paste the formula, or (2) your status field is something other than {Status}. If that’s the case, you’ll need to adjust the field accordingly.

Did you get an ‘invalid formula’ or an ‘unknown field’ error? That will tell us where the problem is…

[Madeline_O_Conn]

W_Vann_Hall:

IF( AND( {Status}=‘Pending’, DATETIME_DIFF( TODAY(), LAST_MODIFIED_TIME( {Status} ), ‘days’ )>10 ), ‘ :rotating_light: ’ )

i had retyped it but just tried copy/paste and it worked!

[Madeline_O_Conn]

thank you so so so much!