How do I update the year based on status change?

Tab

I want to have the year updated when the project status changes to Complete or Cancelled to insert the current year to be automatically updated in the Archive Year column. The column is currently set as a single dropdown with all the years listed, but I want to build a formula to automatically change the column to update when the project status changes. Do I need to change the dropbox to a text/number? I have a workflow created to move the row once a year when the Archive Year is for the prior year. I know it can probably be easy to setup a workflow on this, but I would like to use a formula to have the column updated for the current year whether its 2022, 2023, etc. based on the current year project is complete or cancelled.

Katy H

This would actually be better pulled off by using an automation and a formula.

The automation would record a date in a separate column, let's call it "Date Closed" and then the Archive Year formula would be, =YEAR([Date Closed]@row)

Tab

Thanks. I figured the formula to update if the status is complete or cancelled with an end date to input the year in the archive column as:

=IF(OR([Project Status]@row = "Complete", [Project Status]@row = "Cancelled"), YEAR([Actual End]@row))