Range contains specific date
=COUNTIFS(range,date)>0
To test if a range contains a specific date, you can use the COUNTIFS function. In the example shown, the formula in F5, copied down, is:
=COUNTIFS(dates,E5)>0
where dates is the named range B5:B16
How this formula works
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:
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:
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:
