Helper Column to compare Dates

Ashley Johnson

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!

Mike Wilday

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:

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Mike Wilday

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:

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Ashley Johnson

It worked. Thank you!