Task Health: formula dependents on Creation Date
Hello there,
I need to automate the Task Health column basing on the following criteria:
Blue: creation date is in the last 3 months
Green: creation date is between the last 3 and 6 months
Yellow: creation date is between the last 6 and 9 months
Red: creation date is more than 9 months old
Thanks for your help
Best Answer
-
Hi @a.antuzzi,
Try this formula, it needs to be copied into the health column, once added to the first row right click and select the "Convert to Column Formula" to apply it to all rows.
=IF(DATEONLY(Created@row) > TODAY() - 90, "Blue", IF(DATEONLY(Created@row) > TODAY() - 180, "Green", IF(DATEONLY(Created@row) > TODAY() - 270, "Yellow", "Red")))
The formula is crude in terms of the using the number of days rather than precise months.
John
Answers
-
Hi @a.antuzzi,
Try this formula, it needs to be copied into the health column, once added to the first row right click and select the "Convert to Column Formula" to apply it to all rows.
=IF(DATEONLY(Created@row) > TODAY() - 90, "Blue", IF(DATEONLY(Created@row) > TODAY() - 180, "Green", IF(DATEONLY(Created@row) > TODAY() - 270, "Yellow", "Red")))
The formula is crude in terms of the using the number of days rather than precise months.
John
-
My column name is "Creation Date" so I modified your formula like this:
=IF(DATEONLY(Creation Date@row) > TODAY() - 90, "Blue", IF(DATEONLY(Creation Date@row) > TODAY() - 180, "Green", IF(DATEONLY(Creation Date@row) > TODAY() - 270, "Yellow", "Red")))
... but I get #UNPARSEABLE...
When I renamed the column to remove spaces, the formula worked properly.
How can I edit the formula in order to keep spaces in the column name?
Thanks
-
hi @a.antuzzi,
when you have a space in the column name, you need to have the columns in [], like this : [Creation Date]
an example :
best regards,
Florian
-
Great! thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!