Count no of cells containing date

**princ_wns** · 04-15-2012, 06:53 PM

Hi All,

I have a range of In which some cells contain date as their value i have to count those cells although i have a way to count these cells by using for each loop but i dont want to use loop. Is their any formula that i can use to count cells with date.

Regards.

**Rick Rothstein** · 04-16-2012, 01:04 AM

*** See Edit Note Below ***

I don't think there could be a formula as dates look like numbers to Excel, so there is no way for it to distinguish between the two. It is ashame the CELL function is not array aware or we could do something like this...

=SUMPRODUCT(--(LEFT(CELL("format",A1:F12))="D"))

But, alas, that does not work because the only thing CELL sees for its second argument is A1.

EDIT NOTE
-------------------
Actually it just occurred to me... the above suggestion would not work even if the CELL function was array aware! All CELL("format",A1) returns is the Cell Format for A1... it says nothing about the actual value in A1. You could format A1 with a Date format, but that does not mean the cell actually contains a date value... the cell could contain a text constant or, for that matter, be completely blank and CELL("format",A1) would still return a value starting with the letter "D".

**Admin** · 04-16-2012, 09:01 AM

Hi,

You could try something like this

Code:

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** · 04-16-2012, 11:17 AM

Originally Posted by Admin

Hi,

You could try something like this

Code:

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.

**Admin** · 04-16-2012, 02:23 PM

**Rick Rothstein** · 04-16-2012, 10:37 PM

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)