Okay, I think this UDF (user defined function) works, but with all the non-working attempts it took to get me to it, I won't say I'm 100% sure it works without ever returning a false-positive every now and then...
Code:
Function DateCount(Rng As Range) As Long
Dim R As Range
For Each R In Rng
If Len(R.Text) Then
If IsDate(R.Value) And R.NumberFormat Like "*[dDmMyY]*" Then DateCount = DateCount + 1
End If
Next
End Function
If anyone comes across a value that is falsely counted as a date, please reply with it to this thread so an attempt can be made to patch the formula for it.
NOTE: How to Install UDFs
-----------------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NDateCount ust like it was a built-in Excel function. For example,
=DateCount(A1:C9)
Reply With Quote
Bookmarks