Returning Harvey Balls after comparing dates in two columns

Tabitha W.

Hi SS Community,

I'm trying to return either a red, yellow or green ball after comparing the length of time betweentwo dates.

Here is my example:

I want to say that the lagtime column turns yellow after 2 days or 48 hours if the date it was received and the date it was assigned has reached 48 hours, then red if 3 days or 72 hours has been reached or has passed.

I'll later turn it green if another column turns green based on another formula.

I started using "if and" but started confusing myself.

Thanks in advance!

Nic Larsen

=IF([Received by AA]@row - [Assigned To OSU]@row >= 3, "Red", "Yellow")

Nic Larsen

=IF([Received by AA]@row - [Assigned To OSU]@row >= 3, "Red", "Yellow")

Tabitha W.

Thanks so much for your response. The formula is semi working.

It's turning blanks yellow (where no date has been entered yet) and not turning red for days where the time lapse is 3 days or greater.

Nic Larsen

I added a check to see if the 1st column is empty, if so, it'll just stay blank. If it is filled it will move on. Oops, I had the formula backwards subtracting 1st column from 2nd. I've switched that and it should work now.

=IF(ISBLANK([Received by AA]@row), "", IF([Assigned To OSU]@row - [Received by AA]@row >= 3, "Red", "Yellow"))

Tabitha W.

Thank you so much- you're amazing!