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)

​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)       Read More

​= TODAY function returning the wrong values” />