Counting dates
 

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

Counting dates
 

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

"Tendresse" wrote in message
...
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

Counting dates
 

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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

Counting dates
 

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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

Counting dates
 

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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






--

Dave Peterson

Counting dates
 

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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






--

Dave Peterson

Counting dates
 

Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

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" wrote:

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

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

"Tendresse" wrote in message
...
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" wrote:

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

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)=ROW S($1:1),1)))

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

Biff

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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" wrote:

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

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

"T. Valko" wrote:

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)=ROW S($1:1),1)))

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

Biff

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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" wrote:

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson

Counting dates
 

You're welcome. Thanks for the feedback!

Biff

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

"T. Valko" wrote:

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)=ROW S($1:1),1)))

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

Biff

"T. Valko" wrote in message
...
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

"Tendresse" wrote in message
...
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" wrote:

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

"Dave Peterson" wrote in message
...
Did you see this instruction from Biff?

Copy down 12 rows for the 12 months



Tendresse wrote:

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" wrote:

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


Tendresse wrote:

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,

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"Tendresse" wrote in
message
...
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






--

Dave Peterson


--

Dave Peterson