I am searching a range of cells that contain dates. I want the formula to examine if any of the said dates are before a specified date. If the statement is true I want the formula to put yes in the cell, if false i want the formula to put no in the cell
=IF(COUNTIF('ESU1'!D3:D50,"<date(2014,1,1)"),"Yes","No")
Based on the cells range there are dates before specified date but returning the fals results of "No"
Change the placement of the second double quotes and use the ampersand &:
=IF(COUNTIF('ESU1'!D3:D50,"<"&DATE(2014,1,1)),"Yes","No")
If <date(2014,1,1) is enclosed within the quotes as you currently have it, it is evaluated as text. Moving the quotes and using the ampersand separates the operator < and the DATE function, which is now evaluated, and now the COUNTIF will do a time comparison and not look for the text <date(2014,1,1).
=IF(COUNTIF('ESU1'!D3:D50,"<"&DATE(2014,1,1)),"Yes","No")– BigBen