Trying to subtract a value from the TODAY() function is generating the wrong result

Question

[Share link to a copy of the spreadsheet][1] [1]: https://docs.google.com/spreadsheets/d/1OFwScEx93z4siZMOQoJB9dtAF1dFEZcnyyj6V1CqF6U/edit?usp=sharing

I think the problem lies within the formatting but I don't know how to get past it. I've been working on it for several hours trying various formattings changing to text versions of a date, and using TO_DATE and more but not working. I'm stumped.

The equation in N7 worked great in 2020 but broke in 2021 "My Question: I'm trying to determine if the value in M7 is less than 7 days from =TODAY() in cell N7. The equation is in cell N7. How can I rework this so it works again?"

This question is only for the value in M7 and the equation in N7. I believe if we can figure it out I can copy to the other cells.

When I click on cell M7 it is showing me the wrong year in the formula bar. Instead of 2020-01-08 is shows 2021-01-08 but you can clearly see is displays 2020-01-08.

• This is where the problem is (I think) but I don't know how to get past it.

To the right of the table in row 6 are equations and the values they return. None of the values work when trying to subtract from the TODAY() function. I've also tried using the NOW() function. I'm open to try other ideas.

I could subtract 366 in the equation and the true would go away but that only works if the row is true. Meaning I would have to change it every time there was a false-positive throughout the year.

I suspect the answer lies in the formatting of column M since I am subtracting that value from TODAY().

Any ideas?

Thanks,

Rick

Answer 1

there is a wrong custom formatting for that cell with forced/static year:

where it should be: