Excel Formula Count cells where value is date

Question

When a cell's value is a date, I need the formula to perform a COUNTIF (or a similar function) on the cell range and increase the counter. Basically, something like this:

=COUNTIF(range, if_date())

I haven't been able to locate a practical test for the if date() portion of the query. Is it possible to test a cell to see if it has a date?

Answer 1

Dates in Excel are just formatted numbers, so this is difficult to do with worksheet functions. The only function that lets you investigate a cell's format is CELL (and you can't apply that to a range, so a helper column would be needed). However, if all you have are dates and blanks or dates and text, you can just use COUNT, i.e.

=COUNT(range)

That counts numbers, therefore if you wish to separate dates from numbers, it won't be sufficient. If so, the number range could be used. For instance, if the dates are all very recent and the numbers in the range are all lower than 10,000, you could use this version to omit the numbers.

=COUNTIF(range,">10000")