Setup Logic for Greater than 18 months

Tab

Need to create logic to check the archive box if a project is in a 'Not Started' or 'On Hold' status >18 months from the Submission date (system generated column). Triggered monthly by when the current month is > 18 months from the Submission date. The checked box will then flag the row to be moved to a new sheet to be archived.

Genevieve P.

Hi @Tab

You can create an IF statement that looks at the date and sees if Today is greater than 548 days (18 months). If it is, it will check the box. If it's not, it will leave the box empty.

=IF(AND(OR(Status@row = "Not Started", Status@row = "On Hold"), TODAY() >= [Date Column]@row + 548), 1, 0)

See: Use Formulas to Perform Calculations With Dates

Then you can set an automation to run once a month to see if the box is checked or not, and Move the row if that condition is met.

See: Create a Time-Based Automated Workflow

Cheers,

Genevieve

Jeff M.

You could use an automation based on the system generated date, instead of creating the checkbox. The automation would be set to run daily or weekly, and the condition would be submission date is in the past + submission date is not in the last 547 days + Status is one of  'Not Started' or 'On Hold'. Then the action would be to move rows...

Tab

@Genevieve P. When I input the info in Smartsheet as this:

=IF(AND(OR([Project Status]@row = "Not Started", [Project Status]@row = "On Hold"), TODAY() >= [Created Date]@row + 548) 1, 0)

I receive the following message:

Genevieve P.

Hi @Tab

My apologies, there's a comma missing before the final 1, 0:

=IF(AND(OR([Project Status]@row = "Not Started", [Project Status]@row = "On Hold"), TODAY() >= [Created Date]@row + 548), 1, 0)

Also, what are your column names in your sheet? You'll want to make sure the [Project Status] and [Created Date] column titles in my formula are adjusted to have the column names that are present in your sheet.

Let me know if that worked! If not, it would be helpful to see a screen capture of your sheet set up, but please block any sensitive data.

Cheers,

Genevieve