Explanation
Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array ), ISNUMBER will return an array of results. In the example, the range C5:C9 contains 5 cells, so the array returned by ISNUMBER contains 5 results:
{FALSE;FALSE;FALSE;TRUE;FALSE}
TRUE values represent numeric values.
We want to know if this result contains any TRUE values, so we use the double negative operator (–) to force the TRUE and FALSE values to 1 and 0 respectively. This is an example of boolean logic , and the result is an array of 1’s and 0’s:
{0;0;0;1;0}
We use the SUMPRODUCT function to sum the array:
=SUMPRODUCT({0;0;0;1;0})
Any sum greater than zero means at least one number exists in the range, so we use “>0” to force a final result of TRUE or FALSE.
Explanation
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")
