Count no of cells containing date

[Admin]

Hi,

You could try something like this

Sub kTest()
    
    Dim rngDate     As Range
    Dim CountDate   As Long
    
    Set rngDate = Range("a2:a" & Range("a" & Rows.Count).End(3).Row)
    
    With rngDate
        .Offset(, 1).EntireColumn.Insert
        .Offset(, 1).FormulaR1C1 = "=cell(""format"",rc[-1])"
        CountDate = Application.WorksheetFunction.CountIf(.Offset(, 1), "D*")
        .Offset(, 1).EntireColumn.Delete
    End With
    
End Sub

I see it doesn't with the format "dd-mmm-yy"

[Rick Rothstein]

Hi,

You could try something like this

Sub kTest()
    
    Dim rngDate     As Range
    Dim CountDate   As Long
    
    Set rngDate = Range("a2:a" & Range("a" & Rows.Count).End(3).Row)
    
    With rngDate
        .Offset(, 1).EntireColumn.Insert
        .Offset(, 1).FormulaR1C1 = "=cell(""format"",rc[-1])"
        CountDate = Application.WorksheetFunction.CountIf(.Offset(, 1), "D*")
        .Offset(, 1).EntireColumn.Delete
    End With
    
End Sub

I see it doesn't with the format "dd-mmm-yy"

I am not sure why you wrote that last sentence as CELL("format",A1) will return "D1" for a cell formatted that way. HOWEVER, your code is not guaranteed to work as the OP wanted... see the EDIT NOTE that I added to my original message in this thread.