Just learning conditional logic and need help with a formula

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.

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.

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.