Using TODAY() in IF function

[tehashi]

Hi,
I am trying to set up a formula that will look at a column of dates i have and give me a "1" if that date is after today's date and leave the cell blank if it is not.
I have tried =IF(X4<=TODAY(),1,"") but it gives me this error;
"Please enter a valid date between 1/1/2005 and 12/31/2099 in the format mm/dd/yyyy"
if i take off the "" it works but any false's print the word FALSE in the cell instead of leaving it blank.

This is supposed to go with another column that does the same, but for dates before today, so i can sort expirations.

I really appreciate any ideas how i could get this to work.

[JBeaucaire]

Your formula works for me (excel 2003). Perhaps post an excerpt of your sheet so we can see yours and why it's not working.

[tehashi]

How should i post an excerpt? just copy a few rows and paste them in a reply?

[tehashi]

Better idea, here is a few rows copy-pasted into a new sheet.
My equations will be going in the Z & AA columns.

[mewingkitty]

I experienced the same problem you did after opening your workbook. Deleting the original cells in Z and AA and then putting your code back in there again got it working.

Your code works! Just gotta get the cells set up properly methinks.

[shg]

You need to delete the data validation in those cells, or change it to Custom, Formula is: =IF(Z4="", TRUE, AND(Z4>0, INT(Z4)=Z4))

Then you can change the cell formula to =IF(X4 <= TODAY(), 1, "")

[tehashi]

Cool, thanks. mewingkitty's advice worked and it seems to be working fine.
Thank you all!