NEED HELP: Health Formula based on start date, end date, and %100 complete needed.

Daniel Crippen

Hi Guys,

I'm really struggling with a smartsheet formula.

This is what I'm attempting:

=IF([Plan Start]@row > TODAY(), "Green", IF([Plan Start]@row < TODAY(), "Yellow", IF([Due Date]@row >[Due Date]@row TODAY(), "Red") OR(IF([% Complete]@row = "1", "Blue")))

Essentially I'm trying to make a formula that says: If It's before start day = blank, if start date or after = green, if 1 week before due date = yellow, if due date or after = red, if % Complete column is 100% = blue

Any help is appreciated

Sam M.

Hi Daniel,

I did some testing and this might work:

The formula I used was:

=IF([% Complete]@row = 1, "Blue", IF([Plan Start]@row > TODAY(), "", IF(AND([Due Date]@row <= TODAY(), [Plan Start]@row < TODAY()), "Red", IF(NOT([Due Date]@row > TODAY(+7)), "Yellow", IF([Plan Start]@row <= TODAY(), "Green")))))

The formula is based on what is being asked:

if % Complete column is 100% = blue

If It's before start day = blank,

 if due date or after = red,

if 1 week before due date = yellow,

if start date or after = green,

Hope it helps,

Sam