Leave field Blank when using IF formula, on Date Row.
Hello,
I would be eternally grateful if someone can help me, I have tried ISBLANK, and "" to leave the collum's empty but its not working.
I have the IF formula to turn Red when the date is nearing 30 days, Yellow when the date is nearing 60 days, and Green when the date is over 60 days. But some fields will be blank; on the blank fields its automatically showing RED. And I need it to just be blank if there is not a date in that field, but I need the formula to still be for the whole column because we will continue to add to the sheet.
Our actual sheet has a lot of information in between the dates like addresses so the "expiration date" column will be empty and now its showing a lot of red everywhere and its throwing off the purpose of the spreadsheet.
=IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row > TODAY(+90), "Green")))
Best Answers
-
add an "IF" statement to the formula. I know you said you tried it but I found I didn't close it out before adding the ,"" at the end.
=IF(ISBLANK([Expire Date]@row), "")
-
Just cover the rest with a >=60
=IF(ISBLANK([Expire Date]@row), "", IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row >= TODAY(+60), "Green"))))
Answers
-
add an "IF" statement to the formula. I know you said you tried it but I found I didn't close it out before adding the ,"" at the end.
=IF(ISBLANK([Expire Date]@row), "")
-
Yes, I have tried that and it doesn't do anything. I've even tried adding it at the beginning, what am I doing wrong?
=IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row > TODAY(+90), "Green", IF(ISBLANK([Expire Date]@row), ""))))
-
I'll recreate exactly what you have and you can just copy/paste and let me know :)
-
That would be amazing! @Amanda Carta
-
Change the order of your formula
=IF(ISBLANK([Expire Date]@row), "", IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row > TODAY(+90), "Green"))))
-
My concern though is what if you have a count of days that = 61-90. It currently would have it blank
-
OMG! It worked!! you are my hero! You have no idea how many hours I spend trying to figure it out.
Can you please help me figure out the blank dates?
-
Just cover the rest with a >=60
=IF(ISBLANK([Expire Date]@row), "", IF([Expire Date]@row < TODAY(+30), "Red", IF([Expire Date]@row < TODAY(+60), "Yellow", IF([Expire Date]@row >= TODAY(+60), "Green"))))
-
Thank you so much Amanda I did that and its perfect now! I really cant thank you enough you made my day. You are awesome and Im so grateful you helped me. Thank you so much!!! I hope you have a great day, seriously... I cant thank you enough
-
Anytime :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 509 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…