Helper Column to compare Dates
Hi,
I am working to figure out a way to compare actual project dates to planned project dates. I linked the actual project dates from our active program view.
If the Actual End date does not pass the Planned End date I want the "State" Column to be green.
If the Actual End date is past the Planned date but within 21 days, I want the "State" Column to be yellow.
If the Actual End date is more than 21 days past the planned date then I want the "State" column to be red.
Can I do this with a formula in the state column or do a helper column? If so, what would the formula be?
Thank you!
Best Answer

You can do this as a formula in the State Column. OR as a column formula which would help prevent it from being edited.
=IF([ACTUAL END]@row <= [Planned End]@row, "Green", IF([Actual End]@row <= [Planned End]@row + 21, "Yellow", IF([Actual End]@row > [Planned End]@row, "red")))
For more on column formulas see:
Answers

You can do this as a formula in the State Column. OR as a column formula which would help prevent it from being edited.
=IF([ACTUAL END]@row <= [Planned End]@row, "Green", IF([Actual End]@row <= [Planned End]@row + 21, "Yellow", IF([Actual End]@row > [Planned End]@row, "red")))
For more on column formulas see:

It worked. Thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!