Skip to main content

I have several tasks where a date field is filled for whatever date that task is completed. I tried to build a formula where if no date field is blank, it sets the date to TODAY() so that I can know when everything is done.

It seems like this formula keeps re-updating to whatever today is everyday. If all the tasks were done Tuesday, it still keeps changing to whatever today is in that IF(***, TODAY(), BLANK()) statement.

Am I doing something to cause the table to think these values are changing or do I need to use an automation trigger to accomplish this?

You want to use LAST_MODIFIED_TIME({Field in Question}) and not TODAY().

The Today function is literal, the Last Modified function will tell you the last time the whole record (or a subset of fields was edited).

So if you want the formula to output the day when several date fields became “not blank”, then your formula might look like:

IF(
AND({Date 1}, {Date 2}, {Date 3}),
LAST_MODIFIED_TIME({Date 1}, {Date 2}, {Date 3}),
BLANK()
)

^ If all dates were filled in on Tuesday the formula will output Tuesday’s date. If you update any of the dates today, the formula will update to today.

If you want the value of this formula to “stick” to the moment when all dates became filled then use the formula above in addition to another regular Date field (not a formula) and use an Automation to copy the formula value into the date field.

Terms & ConditionsAccessibility statement