Forum Discussion
>= TODAY function returning the wrong values
Hi! I'm trying to create a conditional formatting formular that highlights a row in green if both the cell in column E = "y" and the date in column D is any date after or including today. This is the formula I thought would work - I have also tried it with the IF function around it, both versions below:
=AND($E3 = "y", $D3>=TODAY())
=IF(AND($E3 = "y", $D3>=TODAY()), TRUE, FALSE)
It's currently highlighting cells with dates from last year and all sorts, I'm super confused as to why it's not working as expected. Help would be majorly appreciated! We're using it to track when we have new staff incoming - so when I type in "y" and the start date is still incoming, it's green and we can review data easily, then when the start date has passed it drops all conditional formatting (it's to stop us getting confused essentially!)
I have checked that all the cells with dates in are selected as a 'long date' format FYI!
To note: I have two other (working haha) conditional formatting rules in here:
=AND(ISBLANK($E3), ISBLANK($A3)) to turn a row grey if there is nothing in both column A and E (so it's grey until we add a name in (which goes in column A))
=IF(ISERROR(SEARCH("y",$E2)),TRUE,FALSE) to turn a row red if it doesn't have a y in column E (i.e. if we have a new staff member incoming but haven't prepped for them)
The rows marked with green are texts. Date are in fact numbers . You can check the true nature of a value in a cell with ISNUMBER or ISTEXT. Thus, remove the "th" and "nd" bits in the dates and it will work. And perhaps you will have "st" and "rd" somewhere else as well.
Since texts are always greater than a number the check $D3>=TODAY() will always return TRUE.
By the way, you don't need the IF when you set the conditional formatting rule. So, use your first formula.
The rows marked with green are texts. Date are in fact numbers . You can check the true nature of a value in a cell with ISNUMBER or ISTEXT. Thus, remove the "th" and "nd" bits in the dates and it will work. And perhaps you will have "st" and "rd" somewhere else as well.
Since texts are always greater than a number the check $D3>=TODAY() will always return TRUE.
By the way, you don't need the IF when you set the conditional formatting rule. So, use your first formula.
- THANK YOU that was exactly it! such a silly oversight haha 🙂
