Range contains specific date

First, it's important to note first that Excel dates are simply large serial numbers. When we check for a date with a formula, we are looking for a specific large number, not text.

This formula is a basic example of using the COUNTIFS function with just one condition. The named range dates is supplied as the first argument, and the date in column E is supplied as the second argument for the condition:

=COUNTIFS(dates,E5)

With the date 13-Jun-2020 in cell E5, the COUNTIFS function returns 1, so the formula then simplifies to:

=1>0

which returns TRUE.

By checking if the result from COUNTIFS is greater than zero, we also handle cases where the count is greater than 1 (i.e. the date we are looking for appears more than once), as in cell E7. Any positive result will cause the formula to return TRUE. When COUNTIFS returns a count of zero, the formula will return FALSE.

With a hardcoded date

The best way to hardcode a date into this formula is to use the DATE function like this:

=COUNTIFS(dates,DATE(2020,6,13))>0

The DATE function ensures that the correct date is passed into COUNTIFS, without requiring Excel to interpret a date in text format.

Check for today's date

To check for today's date, use the TODAY function like this:

=COUNTIFS(dates,TODAY())>0

Note: the TODAY function will continually update as time passes.

With IF

You can nest this formula inside the IF function as the logical test. For example, to return a final result of "Yes" or "No", you can use IF like this:

=IF(COUNTIFS(dates,E5),"Yes","No")