Count Dates?

[Jay]

I have a range A1:A1500 where the cells have the custom date format
of dd.mm.yy

However, some cells contain text. I need a formula to COUNT the number
of cells that contain dd.mm.yy dates.

I was going to try an array SUM(IF(...1,0) which tested for LEN of 8
(the no. of characters in the date fields). But some of the text
entries could also contain 8 characters.

Can anyone advise how I can count the dates?

Many thanks,.....Jason

 

[Don Guillett]

try
=SUMPRODUCT((ISNUMBER(H2:H22))*1)

 

[Bob Phillips]

A repeat to a similar (same) question a couple of days ago

That is a bit difficult as a date is just a number, so testing the value 1
would say that is a valid date.

This formula counts the cells A1:A10 that have numbers that equate to dates
between 1st Jan 2000 and today. Adjust to suit

=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(--"2000-01-01"&":"&TODAY())),A1:A10,0)))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Dates are just numbers to excel.

You could use:

=count(a1:a1500)

But this will include any other numbers you include in that range, too.

 

[Shane Devenshire]

Hi Jay,

If you want to count the cells that contain dates in a specific format you
will need to use VBA:

Function CountDate(R As Range) As Long
Dim cell As Range
Dim Y As Long
For Each cell In R
If (cell.NumberFormat = "dd.mm.yy") Then
Y = Y + 1
End If
Next cell
CountDate = Y
End Function

Cheers,
Shane Devenshire

 

[gujjar]

Just to introduce another function!
This is just opposite to Don's suggestion which is the most apt solution for
your problem

=SUMPRODUCT(ISTEXT(A1:A1500)*1)
/* will count the text values in a range */