Leave field Blank when using IF formula, on Date Row.

mmota

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")))

Amanda Carta

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), "")

Amanda Carta

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"))))

Amanda Carta

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), "")

mmota

@Amanda Carta

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), ""))))

Amanda Carta

I'll recreate exactly what you have and you can just copy/paste and let me know :)

mmota

That would be amazing! @Amanda Carta

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"))))

Amanda Carta

My concern though is what if you have a count of days that = 61-90. It currently would have it blank

mmota

@Amanda Carta

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?

Amanda Carta

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"))))

mmota

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

Amanda Carta

Anytime :)