Count Dates?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 03 Feb 2008 13:43 GMT

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

Reply to this Message

Don Guillett - 03 Feb 2008 13:49 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have a range A1:A1500 where the cells have the custom date format
> of dd.mm.yy
[quoted text clipped - 9 lines]
>
> Many thanks,.....Jason

Reply to this Message

Bob Phillips - 03 Feb 2008 14:44 GMT

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

Signature

---
HTH

Bob

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

>I have a range A1:A1500 where the cells have the custom date format
> of dd.mm.yy
[quoted text clipped - 9 lines]
>
> Many thanks,.....Jason

Reply to this Message

Dave Peterson - 03 Feb 2008 15:05 GMT

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.

> I have a range A1:A1500 where the cells have the custom date format
> of dd.mm.yy
[quoted text clipped - 9 lines]
>
> Many thanks,.....Jason

Signature

Dave Peterson

Reply to this Message

Shane Devenshire - 03 Feb 2008 22:52 GMT

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

> I have a range A1:A1500 where the cells have the custom date format
> of dd.mm.yy
[quoted text clipped - 9 lines]
>
> Many thanks,.....Jason

Reply to this Message

gujjar - 07 Feb 2008 10:04 GMT

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

> I have a range A1:A1500 where the cells have the custom date format
> of dd.mm.yy
[quoted text clipped - 9 lines]
>
> Many thanks,.....Jason

Reply to this Message