Need help with RYG status formula.

Jamy Crum

Hi,

I am using this formula currently to change a harvey ball based on dates. It is currently working, but I am modifying my sheet to include rows that I want to ignore for now, meaning that some date fields will be blank. I would like for the formula to return no harvey ball color when there are no dates in the cells to calculate. This seems simple but I appear to be having trouble with it.

Can anyone offer any guidance?

This is the working (existing) formula:

=IF([Next PM Due Date]@row < TODAY(), "Red", IF([Next PM Notification (Due Within 30 Days)]@row <= TODAY(), "Yellow", IF([Next PM Due Date]@row > TODAY(), "Green")))

If I clear out the dates using this formula, the "Status" cell will still maintain the last harvey ball color in it. I would like for it be blank when no dates are entered.

Thanks!

David Joyeuse

Hi @Jamy Crum

the easiest way is to add this IF statement prior to your formula above:

=IF(AND(ISBLANK([Next PM Due Date]@row),ISBLANK([Next PM Notification (Due Within 30 Days)]@row)),1,[your formula here])

Hope it helped!

David Joyeuse

Hi @Jamy Crum

the easiest way is to add this IF statement prior to your formula above:

=IF(AND(ISBLANK([Next PM Due Date]@row),ISBLANK([Next PM Notification (Due Within 30 Days)]@row)),1,[your formula here])

Hope it helped!

Jamy Crum

Hi @David Joyeuse

Thanks for the fast response.

The formula worked perfectly with one minor modification. It was returning a "1" when no date was present so I just had to change that to " ".

Again, thanks for the quick response. You saved me a lot of time!

David Joyeuse

Oops, my mistake!

Happy to learn it helped you :)