Health Formula

EMAT

I'm currently trying to create a formula that will allow me to update the health status of tasks using both the due date data and the status of the work. I've tried looking at other discussion forums, but using those formulas does not seem to be working for me as they are causing errors. Is there anyone out there that's particularly good at creating formulas that can help me?

Screen Shot 2023-01-29 at 9.10.55 AM.png

Devin Lee

The formula below should work.

=IF([Work Progress]@row = "COMPLETE", "Blue", IF([Work Progress]@row = "DELAYED", "Yellow", IF([Work Progress]@row = "CANCELLED", "Blue", IF([Work Progress]@row = "IN PROGRESS", IF(AND(TODAY() < [Start Date]@row, [End Date]@row < TODAY()), "Red", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red")), IF(TODAY() < [Start Date]@row, "Green", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red"))))))

=IF([Work Progress]@row = "COMPLETE", "Blue", IF([Work Progress]@row = "DELAYED", "Yellow", IF([Work Progress]@row = "CANCELLED", "Blue", IF([Work Progress]@row = "IN PROGRESS", IF(AND(TODAY() < [Start Date]@row, [End Date]@row < TODAY()), "Red", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red")), IF(TODAY() < [Start Date]@row, "Green", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red"))))))

Nick Korna

Hi @EMAT,

What results are you looking to get based on what criteria?

If you can give some examples then we should be able to help out.

EMAT

I've written it out based on how I'm hoping the Health status will react automatically with symbols based on criteria in other cells. Here's what I've got:

Does this help?

Devin Lee

The formula below should work.

=IF([Work Progress]@row = "COMPLETE", "Blue", IF([Work Progress]@row = "DELAYED", "Yellow", IF([Work Progress]@row = "CANCELLED", "Blue", IF([Work Progress]@row = "IN PROGRESS", IF(AND(TODAY() < [Start Date]@row, [End Date]@row < TODAY()), "Red", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red")), IF(TODAY() < [Start Date]@row, "Green", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red"))))))

=IF([Work Progress]@row = "COMPLETE", "Blue", IF([Work Progress]@row = "DELAYED", "Yellow", IF([Work Progress]@row = "CANCELLED", "Blue", IF([Work Progress]@row = "IN PROGRESS", IF(AND(TODAY() < [Start Date]@row, [End Date]@row < TODAY()), "Red", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red")), IF(TODAY() < [Start Date]@row, "Green", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), "Yellow", "Red"))))))

EMAT

That worked! Thank you!