Setup Logic for Greater than 18 months
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.
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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...
-
@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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!