Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Issue with Formula updating the trigger date

Topic Labels: Formulas
Solved
Jump to Solution
381 1
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

1 Solution

Accepted Solutions

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.

See Solution in Thread

1 Reply 1

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.