Last modified time field

The last modified time field type is a computed date field that automatically returns the most recent date and time that a record was modified by a user in a non-computed field (i.e., a field that a user can edit directly).

A related feature is the LAST_MODIFIED_TIME() function, which can be used in formulas to return the date and time of the most recent modification made by a user in a non-computed field. If you only care about changes made to specific fields, you can include one or more field names in your LAST_MODIFIED_TIME() function, and it will just return the date and time of the most recent change made to any of the specified fields, e.g. LAST_MODIFIED_TIME(Status,{Due Date}).

Customizing the last modified time field

Last modified time and other computed fields

Last modified time and linked record fields

Last modified time and undo

Last modified time and blank values

Customizing the last modified time field

When customizing your last modified time field from the field customization menu, you can adjust both which specific values are returned (in the Fields tab) and how the values are formatted (in the Formatting tab).

By default, the last modified time field will be set to show All editable fields, meaning that it will show the most recent date and time that any non-computed field in the table was modified by a user.

If you'd like, you have the option to limit the field so that it will only show the last time that a specific field or fields were modified. Just click the Specific fields... option, then select one or more fields from the popup that appears—the last modified time field will then show the last time any of the specified fields were edited.

After confirming the selected fields, the field customization menu will show the specific fields you've chosen. You can edit your selections by clicking the Change selected fields button.

You can also adjust the formatting of the field values by going to the Formatting tab. From here, you can pick your preferred date format, whether or not you want to include a time, etc.

Last modified time and other computed fields

The last modified time field type and the LAST_MODIFIED_TIME() function both reflect modifications made by a user to editable fields. They do not reflect changes that occur automatically in computed fields, i.e., any fields where the user doesn't directly edit the cell values and the values are instead computed by Airtable—such as formula fields or rollup fields.

Some computed fields can change values as a result of a user making a modification elsewhere. If the value of a computed field changes when the user makes a modification, then you can use formula fields and different combinations of formula functions to find out when changes were last made that affected that computed field.

For example: if you're trying to figure out when the value in a formula field changed most recently, you can often just include the fields used in the original formula as parameters in the LAST_MODIFIED_TIME(). To get the last modified time for a formula field using WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10')
you could create another formula field using LAST_MODIFIED_TIME({Assignment date}, {Due Date}). This will work because changes to the WORKDAY_DIFF() formula field occur whenever modifications to the Assignment date or Due date field have been made.

Last modified time and linked record fields

The value of last modified time will change if you add or remove a record from a linked record field, but it will not change if the fields associated with the linked record(s) have been changed—for example, changing the primary field of the linked record or any other fields that live in another table will not update the value of last modified time. This is because you have not changed which record you have linked to, even if its values in the other table have been modified.

If you would like to know when the value of a linked record field type has changed, you will need to create a new field in the linked table first—either a last modified time field, or a formula field using LAST_MODIFIED_TIME(). You can then use either a rollup with a MAX(values) or a lookup field to pull the last modified time for the linked record(s) into your table.

Last modified time and undo

If you modify a record and then undo that modification (using Ctrl Z ), the value for last modified time will be the time that the undo action was performed— not for the value of last modified time that existed before performing the action that was undone.

Another way to think about this is that the value for last modified time never goes backward in time.

Last modified time and blank values

When no cells in a record have a last modified time, the value of the last modified time field or LAST_MODIFIED_TIME() will be blank. This can occur for a few reasons.

1. No users have yet made any modifications

When a new blank record is created that has no user-created values, none of the fields will have any last modified times, because no users have yet made any modifications.

2. The record predates the last modified time feature

Last modified time starts tracking from when the last modified time feature was enabled. (For longtime users, this will be sometime in May 2019, when we officially launched this feature.) Any old records that were in your bases before last modified time was enabled will return blank values in a last modified time field or for LAST_MODIFIED_TIME().

In either case, any new modifications made to the records will overwrite the blank value with the date and time of the most recent modification.

Blank values causing formula errors

If LAST_MODIFIED_TIME() returns a blank value, and you're using LAST_MODIFIED_TIME() in a formula function that requires a date value (e.g. DATETIME_FORMAT() or TIMESTR()) the formula function will not evaluate properly, which can cause error messages.

You can account for errors caused by blank last modified time values by wrapping your function in an IF statement that checks for errors. For example, instead of TIMESTR(LAST_MODIFIED_TIME()), you could try IF(ISERROR(TIMESTR(LAST_MODIFIED_TIME())),BLANK(), TIMESTR(LAST_MODIFIED_TIME())).