Just learning conditional logic and need help with a formula

I would like a formula for this: When the status of a record is Project Complete for 3 workdays, change the status to Archive.

Unfortunately this isn’t possible via formulas. Formula fields can only control their own output. They can’t affect the contents of other fields.

This could be done via an automation, though. If you don’t already have a field where you’re tracking the date that the project was completed, add a “Last modified time” field that only tracks changes to the status. I’ll refer to this field as {Status Time} from now on. (If you do have a field already, replace {Status Time} in the formula below with your field’s name.) With that in place, add a formula field using this formula:

IF({Status Time}, AND(Status = "Project Complete", WORKDAY_DIFF({Status Time}, NOW()) >= 3))

That will output a 1 when the {Status} is “Project Complete” and 3 or more workdays have passed since that status was set, and a 0 (or empty field) at all other times. I’ll call this formula field {Archive Trigger}.

Make a new automation using the “When record matches conditions” trigger, with the condition being that the {Archive Trigger} value is 1.

Add an {Update record} action. The record to update will be the one from the trigger step. Add the {Status} field as the only field to update, and enter “Archive” for that field’s value.

Turn on the automation, and you’re set! This won’t affect any existing “Project Complete” records that have passed the 3-workday threshold, but will react to all new ones from that point forward.

Justin,

Thank you so much. I can’t wait to set it up!

Blessings,

Lisa

Hi Justin!

I followed your instructions but I am not getting a 1 when the status is Project Completed*. Any ideas?

Thanks,

Lisa

Remember the dual-condition of that formula. The only fields I see in your screenshots have today’s date. The formula will only output a 1 when both conditions are met, and one of the conditions is that the {Last Modified Status} date is more than 3 working days ago, which isn’t the case in those examples.

Justin:

I misread your first email and thought the formula outputted a 1 when the status appears as “Project Completed*”, then after 3 workdays it would change the status to archive. I understand now.

Thanks for your help!

Lisa