Count no of cells containing date

[princ_wns]

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]

*** 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]

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.

[Admin]

OK. So it gives the actual number format of the cell rather than what the cell contains.

@ Prince, disregard my previous post.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

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...


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)