Health Status Formula

Carrie Lucas

I need some help with writing a formula to provide health status based on 2 dates as follows:

"Estimated Completion Date" is greater than today and "Actual Completion Date" is blank - Blue

"Estimated Completion Date" is less than today and "Actual Completion Date" is blank - Red

"Actual Completion Date" is <=> today - Grey

Also, if I change the "Health Status" column to Symbols will the colors actually show as such instead of text?

Scott Peters

Hello @Carrie Lucas - Try this:

=if(and([Estimated Completion Date]@row>today(), [Actual Completion Date]@row=""), "Blue", if(and([Estimated Completion Date]@row<today(), [Actual Completion Date]@row=""), "Red", "Grey")))

This does not account for scenarios where Estimated Completion Date is blank, but hopefully it gets you started.

Nic Larsen

Try:

=IF(AND([Estimated Completion Date]@row > TODAY(), ISBLANK([Actual Completion Date]@row)), "Blue", IF(AND([Estimated Completion Date]@row < TODAY(), ISBLANK([Actual Completion Date]@row)), "Red", IF([Actual Completion Date]@row = TODAY(), "Gray", "")))

Carrie Lucas

Discovered this had one extra parenthesis on the end but once removed it works. Greatly appreciate the help. Thanks!