Excel 2003 - How to count dates in row, including multiple dates in cell

Question

I have some data that looks like this:

However, I would like the total to exclude any text entries (the formula should count dates only). Because some boxes can have multiple dates in, I appreciate that those boxes will be counted as text. Currently I am using this:

=SUMPRODUCT(--(B2:D2<>""),LEN(B2:D2)-LEN(SUBSTITUTE(B2:D2,",",""))+1)

Which counts the multiple dates in cells perfectly, but is also counting the text entries. Thinking about it I could ask it to exclude instances which match 'Holiday' or 'Left'etc, but any pointers would be appreciated.

EDIT: In the end I went with a compound (?) countif formula like this:

=COUNTIF(B3:D3,"Holiday")+COUNTIF(B3:D3,"Left")

and minused the total from the total yielded from the sumproduct. Not an elegant solution, but it does work!

Answer 1

Well, as there's been no other suggestions, I will list this as the answer I went with. Using something like:

=COUNTIF(B3:D3,"Holiday")+COUNTIF(B3:D3,"Left")

will help identify any specific entries I don't want to count in my total. I can then use that to minus from the total achieved with the original formula. Because my text entries come from a data validation list, this is a usable solution, but it's not ideal if you were looking to exclude any text entries (apart from multiple dates).

Answer 2

did you try using COUNTIF with wildcards. Since your dates always contain a "/", you can try -

COUNTIF(A1:D1,"*/*")