I need help with a conditional formatting problem when moving from Excel to Google Sheets. I built a tracking system that changes cell colors based on how old contact dates are.
My current setup uses these rules in order:
Cell contains “SKIP” - Format as black
Cell contains “NEW” - Format as blue
Formula: AND(C6<=TODAY()-30, C6>TODAY()-45) - Format as orange
Formula: C6<=TODAY()-45 - Format as red
Formula: C6>TODAY()-30 - Format as green
The system works great in Excel. Contacts from the last 30 days show green, 30-45 days show orange, over 45 days show red. I can mark items as “SKIP” for black or “NEW” for blue.
The problem: When I upload to Google Sheets, empty cells show up red instead of staying blank. In Excel they look clear like I want them to. I tried adding ISBLANK(C6) with no fill formatting but it doesn’t work since the cells have formulas in them.
How can I make empty cells stay blank in Google Sheets? There must be a way to detect when a cell has no actual date value even though it contains a formula.
Ugh, this Google Sheets bug is so annoying. Don’t modify all your rules separately - just add NOT(OR(C6="",C6=0)) at the start of each formula with AND. For red, it becomes =AND(NOT(OR(C6="",C6=0)), C6<=TODAY()-45). Way simpler than rebuilding everything.
Had the exact same frustrating issue when migrating our project tracking sheet. Google Sheets treats empty cells as date serial number zero, which equals January 1, 1900 - way older than 45 days, so your red rule fires. I fixed it by scrapping the IF conditions inside each rule and using a helper column instead. The helper column figures out each cell’s actual status first, then I reference that in my conditional formatting. Try this formula in column D: =IF(OR(C6=\"\",C6=0),\"BLANK\",IF(C6>TODAY()-30,\"GREEN\",IF(C6>TODAY()-45,\"ORANGE\",\"RED\"))). Then set your conditional formatting to look for "BLANK", "GREEN", "ORANGE", or "RED" text in column D instead of evaluating dates directly. This completely sidesteps Google Sheets’ weird empty cell behavior and gives you total control over formatting.
Hit this exact nightmare during a client data migration last month. Google Sheets treats empty formula cells as epoch zero dates, so they’re ancient and trigger your red condition. Don’t bother modifying each conditional formatting rule separately - there’s a better way. Create a custom formula that handles empty cells first, then wrap your existing logic inside it. For your red rule, try =IF(OR(C6="",ISNA(C6),C6=0),FALSE,C6<=TODAY()-45). You need to check multiple empty states because Google Sheets interprets emptiness differently depending on how the cell became empty. Use this same wrapper pattern for your orange and green rules. It keeps your existing logic intact while forcing Google Sheets to ignore truly empty cells instead of treating them like ancient dates.
Been dealing with this exact headache for years across different projects. Google Sheets treats empty formula cells differently than Excel - your formulas hit empty cells and Google Sheets interprets them as zero dates, making them super old and triggering your red formatting.
Sure, you could modify each formula like Pete suggested, but there’s a cleaner approach. Skip the conditional formatting quirks and automate the whole thing.
I built something similar using Latenode that monitors my contact tracking sheets. It automatically applies the right formatting based on date values and handles empty cells perfectly. No more platform compatibility issues.
The workflow checks for actual date values, ignores empty cells completely, and updates formatting in real time. You can even add automation like alerts when contacts hit certain age thresholds.
Way more reliable than making complex conditional formatting work across different spreadsheet platforms.
This conditional formatting mess between Excel and Google Sheets drives me crazy too. Google Sheets treats empty cells as zero dates, making them appear older than your 45-day cutoff.
Everyone’s suggesting formula tweaks, but I’d skip conditional formatting altogether. These cross-platform issues never stop.
I fixed this same problem using Latenode. Built a workflow that reads the sheet, applies proper color coding, and writes it back. No more empty cell headaches or platform fights.
It handles date comparisons cleanly, ignores truly empty cells, and sends notifications when contacts hit different age brackets. Way cleaner than fighting spreadsheet quirks.
You can also add automatic follow-up scheduling based on contact age. Much more powerful than static conditional formatting.
I encountered a similar issue when transitioning from Excel to Google Sheets. It seems that Google Sheets interprets empty cells as zeros, leading to unexpected behavior with date formatting. To resolve this, I suggest modifying your existing conditional formatting rules by integrating checks for empty cells. For example, adjust your formula for red formatting to: AND(C6<>"", C6<=TODAY()-45). Likewise, update the orange and green rules accordingly. This adjustment ensures that your formatting only applies when there are valid dates in the cells. This should help maintain the clarity you desire for empty cells.
google sheets treats empty cells diff than excel. try wrapping yer main formulas with IF(ISBLANK(C6), FALSE, your_formula_here) instead of adding ISBLANK separately. this fixed the same issue i had with date conditional formatting messin up on blank cells.