ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Counting Nr of Entries in a Column which are Formatted as Date orText or Number (https://www.excelbanter.com/excel-discussion-misc-queries/453967-counting-nr-entries-column-formatted-date-ortext-number.html)

[email protected]

Counting Nr of Entries in a Column which are Formatted as Date orText or Number
 
Dear Excel Community,
I have a column of 600 data entries. It is in cells B11 to B610. In the first few rows above the column I'd like to summarise the number of cells which are (a) date format - not too fussy which particular date format, just want to know that it is a date (b) number format (c) text format. I can get blank using =countblank(B11:B610) - with there was a CountDate() and CountNumber() formula - any help appreciated.

(I actually have tonse of columns - but answers to the above will assist. I've been working-around matters with AutoFilters - but a formula approach would be so helpful.

Thanks in advance, Gerry.

Jianguo Li

Counting Nr of Entries in a Column which are Formatted as Date or Text or Number
 
It may have no worksheet function to do this, but VBA have a function
"isdate".
Define a function:

Function CountDate(ra As Range)
Dim rCel As Range, i%
For Each rCel In ra
If IsDate(rCel) Then i = i + 1
Next
CountDate = i
End Function

ra is the range you want to count date.

дÈëÏûÏ¢
...
Dear Excel Community,
I have a column of 600 data entries. It is in cells B11 to B610. In the
first few rows above the column I'd like to summarise the number of cells
which are (a) date format - not too fussy which particular date format,
just want to know that it is a date (b) number format (c) text format. I
can get blank using =countblank(B11:B610) - with there was a CountDate()
and CountNumber() formula - any help appreciated.

(I actually have tonse of columns - but answers to the above will assist.
I've been working-around matters with AutoFilters - but a formula approach
would be so helpful.

Thanks in advance, Gerry.




killme2008

It may have no worksheet function to do this, but VBA have a function
"isdate".
Define a function:

Function CountDate(ra As Range)
Dim rCel As Range, i%
For Each rCel In ra
If IsDate(rCel) Then i = i + 1
Next
CountDate = i
End Function

ra is the range you want to count date.


All times are GMT +1. The time now is 08:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com