Excel - Count the number of Dates

[TKT-Tang]

Several dates (amidst other data formats such as numbers, text, errors
and logicals) are listed along column A:A. Devise a formula to count
the number of dates (formatted as yyyy-mm-dd) thereof.
Regards.

 

[Jonathan Rynd]

(e-mail address removed) (TKT-Tang) wrote in

Devise a formula to count
the number of dates (formatted as yyyy-mm-dd) thereof.

Look at the "CELL" worksheet function.

 

[TKT-Tang]

Mr. Jonathan Rynd,

Look at the "CELL" worksheet function.

Thank you for your reply.

There are several variations of the CELL worksheet function. I am at
sea with respect to the most appropriate choice (that would be
applicable to meet my query) amidst the given variations.

It behooves me to request futher clarification.

Regards.

 

[Jonathan Rynd]

(e-mail address removed) (TKT-Tang) wrote in

There are several variations of the CELL worksheet function. I am at

Unfortunately, I didn't know the CELL function as well as I thought. It
does not work for dates formated as yyyy-mm-dd. In order to determine
this you will need to use a visual basic user defined function (VB UDF).

Choose Tools/Macro/Visual Basic Editor
Choose Insert/Module

Type or paste the following:

Function get_cell_format(r As Range)
Dim a() As String
Dim i As Integer, j As Integer
If r.Count = 1 Then
get_cell_format = r.NumberFormat
Else
ReDim a(r.Rows.Count, r.Columns.Count)
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
a(i - 1, j - 1) = r(i, j).NumberFormat
Next j
Next i
get_cell_format = a
End If
End Function

Say your data is in a1:a10.
Select a blank area of your spreadsheet, (say Z1:10) enter the formula
=get_cell_format(a1:a10) instead of pressing enter press control-shift-
enter.
Now to find the number of dates in the range use
=countif(z1:z10,"yyyy-mm-dd")

it doesn't seem to be possible to do it in one step, you need to put the
intermediate values somewhere.