Help now that LAST MODIFIED TIME is available

[Candice_Garcia]

I am soooo excited that this function is available! But now that it’s here, I’m having a hard time thinking through how to set this up for my workflow. Looking for some formula help here…

My scenario: I need track when someone submits a project to a specific status. Currently, I have it set up so I have a view in Airtable that filters out everything except projects in that particular status and then have a Zapier integration to send me an email when there’s something in that view. I then go in to that view and mark whether that project is late, on time, early, etc. based on the due date for that project.

I want to be able to set up a formula to compare the last modified time (only for that one status though) and compare it against the due date for the project and return a result of early, on time, late, etc.

Help?

[JonathanBowen]

Hi @Candice_Garcia - how about this:

Name, Due Date and Status are self-explanatory. Status Last Modified uses the new Last Modified Time with this config:

So, only looking at the changes to the status field. At this point, it captures the last mod date for all statuses.

Let’s say you want to note changes to status where it is “in progress”. The next field - {In progress date} - shows this using the following formula:

IF(Status = 'In progress', {Status Last Modified}, BLANK())

Finally, the {On time days} field gives you the early/late/on time result using:

IF(
  {In progress date}, 
  IF(
    DATETIME_DIFF({Due Date}, {In progress date}, 'days') = 0,
    'On time',
    IF(
      DATETIME_DIFF({Due Date}, {In progress date}, 'days') > 0,
      'Early',
      'Late'
    )    
  )
)

Does this do what you want?

JB

[Candice_Garcia]

Yes! That worked great! Thank you soo much for your help :slightly_smiling_face: