Status Indicator formulas

Enyonam

I have been working on this for two days and I finally decided to ask for help. I am trying to achieve the following:

If Dev Progress is marked as "complete" then "Blue"

If Dev Progress is marked as "In progress" and Today is between Start date and End date then "Green"

If Dev Progress is marked as "not started" and Today is greater than the start date but less than the end date, mark as yellow

If Dev Progress is marked as "delayed" then "Red"

If Dev Progress is marked "not started" and/or the end date is greater than Today mark as "Red"

If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red"

If Dev is marked as "Not Applicable" then show "N/A"

Below is what I have tried so far and it's already not working. I haven't added all the parts of the formula.

=IF([Dev Progress]3 = "Completed", "Blue", IF([Dev Progress]3 = "In Progress", [Dev Start Date]3 < TODAY(), [Dev End Date]3 > TODAY()), "Yellow", IF(OR(AND([Dev Progress]3 = "Not Started"), AND([Dev Start Date]3 < TODAY(), OR(AND([Dev Progress]3 = "In Progress", [Dev End Date]3 < TODAY())))), "Red"))

I tried using the above and modifying it by clicking the cell(@row)

Thank you!

Heath Hilton

Enyonam,

Try this as your formula.

=IF([Dev Progress]@row = "Completed", "Blue", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF([Dev Progress]@row = "Not Applicable", "N/A", ""))))))

I also changed one of the "reds" to purple just to make testing easier.

In "Dev Progress" these are the values that I used. Completed, In Progress, Not Started, Delayed, and Not Applicable.

And here is a shot of all of the cells.

Heath Hilton

Enyonam,

I think it isn't liking the fact that you are looking for "text" in a "date" cell (not 100% though). I also wonder if it was a bit of the order at which it was seeing the order within the formula.

Try this one, I also added another condition for the line that I missed above (If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red").

=IF([Dev Start Date]@row = "", "N/A", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF(AND([Dev Progress]@row = "In Progress", [Dev End Date]@row < TODAY()), "Black", IF([Dev Progress]@row = "Not Applicable", "N/A", IF([Dev Progress]@row = "Completed", "Blue"))))))))

Heath Hilton

Enyonam,

Try this as your formula.

=IF([Dev Progress]@row = "Completed", "Blue", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF([Dev Progress]@row = "Not Applicable", "N/A", ""))))))

I also changed one of the "reds" to purple just to make testing easier.

In "Dev Progress" these are the values that I used. Completed, In Progress, Not Started, Delayed, and Not Applicable.

And here is a shot of all of the cells.

Enyonam

Hi Heath. Thank you that worked perfectly. I added some additional parameters I forgot and everything is working magically.

I tried adding another Not applicable. There are cases where I will not have a start and/or end date.

I added the following line:

If([Dev Start Date]@row = "not applicable", "N/A")

the formula works by itself but when I nest it into the bigger formula it does not work.

It returns a result of "invalid" could you assist?

Heath Hilton

Enyonam,

I think it isn't liking the fact that you are looking for "text" in a "date" cell (not 100% though). I also wonder if it was a bit of the order at which it was seeing the order within the formula.

Try this one, I also added another condition for the line that I missed above (If Dev progress is marked as "in Progress" and Today is greater than the end date mark as "Red").

=IF([Dev Start Date]@row = "", "N/A", IF(AND([Dev Progress]@row = "In Progress", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Green", IF(AND([Dev Progress]@row = "Not Started", [Dev Start Date]@row < TODAY(), [Dev End Date]@row > TODAY()), "Yellow", IF([Dev Progress]@row = "Delayed", "Red", IF(OR([Dev Progress]@row = "Not Started", [Dev End Date]@row > TODAY()), "Purple", IF(AND([Dev Progress]@row = "In Progress", [Dev End Date]@row < TODAY()), "Black", IF([Dev Progress]@row = "Not Applicable", "N/A", IF([Dev Progress]@row = "Completed", "Blue"))))))))

Enyonam

Yep that worked. Thank you so much