if date is greater than date in a column not working

[golffor1]

Hello,
I am trying to calculate how if a date in A2 is greater than any date then do something. I am using the following formula: =IF(A40>$AM:$AM, "YES", "NO"). My issue is that if I change my date to something older than what is in newer than a40 then then yes doesn't change to a no...and visa versa.

 

[Jonmo1]

Try

=IF(COUNTIF($AM:$AM,"<"&A40),"YES","NO")

 

[golffor1]

Try

=IF(COUNTIF($AM:$AM,"<"&A40),"YES","NO")

I tried this and it still did not work.

 

[Jonmo1]

Works for me.

It's likely one or more or all of your dates are not really dates. They may just be text strings that look like dates.
Test with ISNUMBER

=ISNUMBER(A40)
and
=ISNUMBER(AM1) <- Fill this down to end of your data.

Do these return True or False ?

Maybe you could attach a sample workbook.

 

[golffor1]

I just tried to create a sample and looked at my data again. No wonder it didn't work for me, I wasn't looking at the right column. It works for me when I use your original response.