Hey everyone, I need some help with a formatting problem I’m having. I built a spreadsheet in Excel to track contact dates at work. The system uses TODAY() function and conditional formatting to show different colors based on how long it’s been since contact.
Here’s my setup for range C5:C150:
- Text value = REMOVE → Black background
- Text value = ACTIVE → Blue background
- Formula: AND(C5<=TODAY()-30, C5>TODAY()-45) → Orange background
- Formula: C5<=TODAY()-45 → Red background
- Formula: C5>TODAY()-30 → Green background
The issue happens when I upload to Google Sheets. In Excel, empty cells show up clear like they should. But in Google Sheets, any cell without a date gets colored red instead of staying blank.
I tried using ISBLANK(C5) with no fill formatting, but it doesn’t work because the cells aren’t truly empty - they have formulas in them. There has to be a way to make cells with formulas but no actual date values display as blank instead of triggering the red formatting rule.
Anyone know how to fix this Google Sheets formatting issue?
Had the exact same issue when I moved our project tracking from Excel to Google Sheets last year. Google Sheets treats empty cells totally different from Excel, especially with formulas that return blank values. Here’s what fixed it for me: I added an empty cell check to all my conditional formatting rules. Instead of just C5<=TODAY()-45 for red, I changed it to AND(C5<>“”, C5<=TODAY()-45). This stops the rule from hitting cells without actual dates. You’ll need to update every conditional formatting formula with this check. So your orange rule becomes AND(C5<>“”, C5<=TODAY()-30, C5>TODAY()-45). Basically, you’re telling Google Sheets to skip empty cells instead of treating them as some default value that triggers your oldest date rule.
i kno, right? sheets can b so frustrating! the if statement trick is a lifesaver, just like u said. plus, maybe also try using if(c5=“”,“,” instead of isdate, it seems 2 work better sometimes. good luck!
This migration issue trips up tons of people. Google Sheets handles blank formula results totally differently than Excel - that’s why your empty cells are getting the red formatting. Google Sheets treats these as zero values instead of actual blanks. I hit this same problem last month moving our client database. Here’s what fixed it: restructure your conditional formatting order. Put the blank cell condition first, then add your date rules below it. For blanks, use C5=“” with no fill color. This rule needs to be at the top so it runs before the red rule. You’ll probably need to clear and reapply all your formatting rules after this change. Google Sheets can be stubborn about holding onto old logic even after you edit it.