Formula for date that cell was updated

[Caitlyn10]

Hi, is there a formula to pull the date that a specific cell was updated?

For context; I use Airtable to confirm bookings for my business, and do weekly reports on these. The reporting is currently based on 'created date' as I only add them to the system when they are about to be confirmed. However, there has become a need to add them to the system as soon as I receive the booking request so I can follow up on any tentative bookings. However, if they aren't confirmed until the following week or later, they will fall into the wrong reporting week. I'd like to change my reporting filters to be based on 'created date' OR a new formula field that pulls the date the 'Status' field was change to 'Confirmed'.

I have other automation in play to manage tentative bookings, but it's failing and this formula would be a much better solution (if it exists).

Any help would be much appreciated!

[Mike_AutomaticN]

Hey @Caitlyn10,

To achieve your goal, use Airtable's "Last Modified Time" field (that is a specific Field Type) and configure it to track the 'Status' field. This will record the date whenever the 'Status' changes. Use this date for your reporting filters (filter should also be set to include only records where status is “Confirmed”)

I hope this solves your issue. 
Mike, Consultant @ Automatic Nation

[ScottWorld]

@Mike_AutomaticN 

You can’t set a “last modified time” field to only monitor certain statuses within a field. It only monitors the entire field itself, whenever the field changes.

@Caitlyn10 

In order to get the date that a field changed to a certain status, you would need to setup an automation for that. The trigger would be “when a record matches conditions”, and then you would update a date field with today’s date.

However, since Airtable’s automations don’t allow us to perform calculations or use formulas, you would need to create an extra formula field in your base with either the formula TODAY() or NOW().

Then, you would reference that formula field in your automation when you are updating your date field.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Mike_AutomaticN]

Hey @ScottWorld!

That is correct, however I’m assuming that if 2 conditions are applied to the filter (Last Modified is X; AND Status is “Confirmed”) then the lean suggested approach should be enough for the use case!

Mike, Consultant @ Automatic Nation

[TheTimeSavingCo]

If the "Status" field can change to something else after "Confirmed", I'd recommend you create a new Date Time field called "Confirmed At" or something, and an automation that:
1. Triggers when the "Status" is "Confirmed"
2. A "Run Script" action that outputs the current date time

output.set("date", new Date().toISOString())

3. An "Update Record" action that updates the "Confirmed At" field with the output of the "Run Script" action

This'll trigger every time a "Status" is "Confirmed" and paste the current time into the "Confirmed At" field, and any further changes to the "Status" field won't affect the "Confirmed At" time

If "Confirmed" is the last possible Status, then a Last Modified Time field that points at Status like Mike suggested works just fine

[Caitlyn10]

Thanks so much for the suggestions everyone! I wasn't expecting so many responses 😅

@TheTimeSavingCo I think your suggestion is going to suit my case best, but I still need to test it. I'll report back if I have any further questions.

@ScottWorld you mentioned; "You can’t set a “last modified time” field to only monitor certain statuses within a field. It only monitors the entire field itself, whenever the field changes." 

If I had another 'Confirmed' field that only had that status ('Confirmed V2' for example), would using "last modified time" work for that cell? If so, would it work if I had a formula based on the Status field, to populate "Confirmed" as text in the new 'Confirmed V2' field, but only for Confirmed statuses, and anything other than that, it leaves blank. I'd then do my reporting by dates from the 'Last modified field' (based on Confirmed V2), and anything blank would be excluded as it wasn't confirmed so it's not relevant to the last modified calculation.

Apologies if that was poorly explained. Bit of a brain dump 🤣

[ScottWorld]

Hi @Caitlyn10, 

You can't use a formula for your date field. You would need to use an automation.

I would highly recommend avoiding scripting, because it's not necessary and it overly complicates things on your end by bringing in another programming language. This is all doable in a no-code/low-code way with Airtable's native automations, as I outlined above.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Caitlyn10]

Thank you. I appreciate the clarification. I'll look at those suggestions and see how things go.

[Caitlyn10]

Hey @Mike_AutomaticN I think your suggestion is actually the most straightforward. I've created those filters for my reporting and it's showing the correct data. I haven't found any issues so far, but will see if anything pops up. Appreciate you responding!

[TheTimeSavingCo]

re: I would highly recommend avoiding scripting, because it's not necessary and it overly complicates things on your end by bringing in another programming language. This is all doable in a no-code/low-code way with Airtable's native automations, as I outlined above

Ahh yeah, it's all tradeoffs!  This is a simple one liner and beats having an extra field that only exists for the automation; we've all got too many fields as it is!

It's also worth noting that "NOW()" and "TODAY()" impacts the performance of the base too, especially on bases with a lot of records, and so @Caitlyn10 if use this method and don't need the time you should use "TODAY()"!