Counting dates

[Guest]

I have each cell in column A containing meeting dates separated by commas.
For example:

A
(1) 01/02/07, 13/02/07, 14/03/07
(2) 15/02/07, 14/04/07
(3) 09/01/07, 13/02/07

I want to be able to count the number of meetings that took place in each
month. So in the example above:

In January there was 1 meeting
In February there were 4 meetings
In March there was 1 meeting
In April there was 1 meeting

Is there a way i can count the occurrence of a date when multiple dates are
listed in the same cell? Many thanks

 

[T. Valko]

Try this:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4

Copy down 12 rows for the 12 months

Biff

 

[T. Valko]

Ooops!

Hold on there just a second.

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/0"&ROWS($1:1)&"/","")))/4
Copy down 12 rows for the 12 months

That'll get tripped up on double digit month numbers.

Try this version instead:

=SUMPRODUCT(LEN(A$1:A$3)-LEN(SUBSTITUTE(A$1:A$3,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4

Biff

 

[Guest]

Hi T. Valko, thanks for your reply, but unfortunately it doesn't seem to be
working. The result I got was the following:

01/01/1900

Any ideas?

Ta,

 

[Dave Peterson]

Try formatting that cell as General (not a date).

 

[Guest]

Thanks, Dave. We are getting very close. Well, it seems to be counting all
dates in "January" very well.
Now, what do i need to replace in the formula so that it counts other months
as well. Where does it refer to different months?

Many thanks

 

[Dave Peterson]

Did you see this instruction from Biff?

Thanks, Dave. We are getting very close. Well, it seems to be counting all
dates in "January" very well.
Now, what do i need to replace in the formula so that it counts other months
as well. Where does it refer to different months?

Many thanks

 

[T. Valko]

The month numbers are automatically generated by drag copying the formula
down a column. So, if you copy the formula down to a total of 12 cells you
will cover all 12 months.

If you want to have the results go across a row or if you only want certain
months and want to be able to designate those months let us (me) know and we
(I) can modify the formula accordingly.

Biff

 

[Guest]

Thank u so much. That was really helpful. One more little thing though. In
cases where a cell has only one date (instead of several dates separated by
commas), this one date doesn't get counted until i put a comma at the end of
it. Because this spreadsheet will be filled-in by several users, i can't
guarantee that they will always remember to add the comma when there is only
one date needed.

Is there a way around that? Thanks in advance ..

The month numbers are automatically generated by drag copying the formula
down a column. So, if you copy the formula down to a total of 12 cells you
will cover all 12 months.

If you want to have the results go across a row or if you only want certain
months and want to be able to designate those months let us (me) know and we
(I) can modify the formula accordingly.

Biff

 

[T. Valko]

The reason that is happening is that a single date entered in a cell is a
true Excel date which in reality is a NUMBER. It's a NUMBER formatted to
look like a date.

Where there are multiple dates entered in a cell, Excel treats that as TEXT.

Since your sample data shows multiple dates in every cell I assumed every
cell would contain multiple dates so the formula is based on the content of
the cells being TEXT.

The easiest way to fix this is to format the cells as TEXT then the formula
will work with cells that have a single date entered in them.

I don't have time tonight to try to rework the formula. This does complicate
things!

Biff

Thank u so much. That was really helpful. One more little thing though. In
cases where a cell has only one date (instead of several dates separated
by
commas), this one date doesn't get counted until i put a comma at the end
of
it. Because this spreadsheet will be filled-in by several users, i can't
guarantee that they will always remember to add the comma when there is
only
one date needed.

Is there a way around that? Thanks in advance ..

 

[T. Valko]

Ok, you can either format the cells as TEXT and the previous formula will
work or, you can use this array** formula which will handle both true Excel
dates and text:

=SUM(LEN(A$1:A$10)-LEN(SUBSTITUTE(A$1:A$10,"/"&TEXT(ROWS($1:1),"00")&"/","")))/4+SUM(IF(ISNUMBER(A$1:A$10),IF(MONTH(A$1:A$10)=ROWS($1:1),1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Guest]

Thank you very much, Biff. You have been a wonderful support. My worksheet is
working very well now. Much appreciated.

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Thank you very much, Biff. You have been a wonderful support. My worksheet
is
working very well now. Much appreciated.