Printable View
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.:)
*** 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".
Hi,
You could try something like this
I see it doesn't with the format "dd-mmm-yy"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 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.Quote:
Originally Posted by 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/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
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=UgxvIFArksPprylHXYZ4AaABAg
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=UgxLtKj969oiIu7zNb94AaABAg
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=jdPeMPT98QU
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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...
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.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
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)