Returning Harvey Balls after comparing dates in two columns
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!
Best Answer
-
=IF([Received by AA]@row - [Assigned To OSU]@row >= 3, "Red", "Yellow")
Answers
-
=IF([Received by AA]@row - [Assigned To OSU]@row >= 3, "Red", "Yellow")
-
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.
-
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"))
-
Thank you so much- you're amazing!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!