Automate Expiry Alerts with Simple Conditional Formatting Rules

This reader is trying to solve a problem the right way, but the logic has a fundamental mismatch. They want to flag when a year value in F1 is more than two years old, yet the formula they wrote compares that year number to a date. F1 contains the number 2027, not a date. When you compare 2027 to `TODAY()-365*2`, the spreadsheet converts 2027 into a date serial number, roughly the year 1905, which is always far less than today's date. That's why every cell turns red, regardless of the actual year. The fix is straightforward: compare the year in F1 to the current year, not to today's date. A formula like `=F1

The adjacent cell request for a "WARNING" label is equally solvable, but it requires a different approach. Conditional formatting only changes appearance; it cannot insert text. To get that warning, the reader should use an `IF` formula in the neighboring cell: `=IF(F1

What this exchange really highlights is how easily spreadsheet logic trips up even experienced users. The reader understood the concept of comparing years, but the spreadsheet's handling of date serial numbers created invisible friction. That's exactly where AI-native tools can step in. Imagine a system that understands your intent, flag when a year value exceeds two years, without requiring you to debug why 2027 is being read as 1905. The goal isn't to replace the reader's skill; it's to remove the unnecessary cognitive overhead. The formula itself is simple. The confusion came from a quirk of how spreadsheets store time.

For now, the solution is two formulas and one conditional formatting rule. That's it. No workarounds, no hacks. The reader already has the right instinct, they just need to compare apples to apples. Once they do, that red font will only appear when it should. And that's the whole point of automation: it should make your data tell you what matters, not make you guess why it's lying.