Looking for Formula Help - Health Status based on End Date versus Today

MRosko02

Good morning! I am looking for help with a formula as what I have entered returns "Unparseable".

I am looking to return colors based on the below:

If Status is Complete then "Green"

If Status is in Progress but less than TODAY "Red"

If Status is Not Started or In Progress and the End Date is greater than today by 7 days then "Yellow"

If Status is Not Started or In Progress and if End Date is greater than TODAY by 14 days then "Green"

If Status is NA then Blank

If Status is On Hold then Gray

I may just have too many arguments?

Below is how I entered it

=IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", [End Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", [End Date]@row > TODAY(7), "Yellow", IF(Status@row = "In Progress", IF([End Date]@row > TODAY(14), "Green", IF(Status@row = "On Hold", "Gray", IF(Status@row = "NA", "", IF(Status@row = "In Progress", IF([End Date]@row > TODAY(7), "Yellow", IF(Status@row = "Not Started", IF([End Date]@row > TODAY(14), "Green"))))))))))))

Paul Newcome

That would still leave a gap for 7, 8, or 9 days. You can use whatever works best for you. You could use greater than 7 to pick up right off of the yellow, you can adjust the yellow to be less than or equal to 14 days, you can leave it blank, or you can adjust both the yellow and green number of days to meet somewhere in the middle.

Paul Newcome

It looks like your syntax is a bit off. Try this:

=IF(Status@row <> "N/A", IF(Status@row = "On Hold", "Gray", IF(OR(Status@row = "Complete", [End Date]@row> TODAY(14)), "Green", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row<= TODAY(7), "Yellow")))))

One thing I notice you do not account for is if the end date is between 7 and 14 days in the future. If less than 7 then yellow. If more than 14 then green. But what about the week in between?

Eric Law

Hello @MRosko02,

Your IF statements are kind of off, you should max have 3 fields, but your second inbedded if has 4 which makes that unparseable as it is not a valid formula. You should create individual formulas first to see if they work and then start nesting them.

MRosko02

@Paul Newcome Paul, that makes sense. So I should have put greater than 9 days?

Paul Newcome

That would still leave a gap for 7, 8, or 9 days. You can use whatever works best for you. You could use greater than 7 to pick up right off of the yellow, you can adjust the yellow to be less than or equal to 14 days, you can leave it blank, or you can adjust both the yellow and green number of days to meet somewhere in the middle.

MRosko02

@Paul Newcome Thank you so much! That works great!

Paul Newcome

Happy to help. 👍️