Results 1 to 6 of 6

Thread: Count no of cells containing date

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

    Count no of cells containing date

    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.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    *** 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".
    Last edited by Rick Rothstein; 04-16-2012 at 11:06 AM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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"
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    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.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Last edited by DocAElstein; 09-22-2023 at 05:18 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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)
    Last edited by Rick Rothstein; 05-27-2012 at 07:32 AM.

Similar Threads

  1. Replies: 5
    Last Post: 06-15-2013, 12:40 PM
  2. Count Colored Cells Using Excel Formula
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-15-2013, 11:37 AM
  3. automatic Add date in cells
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 01-23-2013, 02:50 PM
  4. Replies: 0
    Last Post: 02-27-2012, 01:07 AM
  5. Lock cells on the basis of date VBA
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 22
    Last Post: 09-27-2011, 03:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •