Really counting DATES
 

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

=COUNTIF(A1:A5000,"1/01/2008")
Yes, I could find it going thru another alike formula =countif(A1:A20,today())
Thanks anyways.

"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

On Jan 18, 5:04*pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Really counting DATES
 

Now to copy down this formula adding a day everytime I'll need your help,
Instead of writting the same formula everytime and just changing the date to
be
count.Thanks.

"Excelfan" wrote:

=COUNTIF(A1:A5000,"1/01/2008")
Yes, I could find it going thru another alike formula =countif(A1:A20,today())
Thanks anyways.

"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Thanks Fred, it works too.
Do you have an answer for my last post ( probably went before yours)
How can I copy down adding a day everytime?
ex your formula 2008,1,1
2008,1,2
2008,1,3 etc, so I dont have to write the formula
every date/day.

"Fred Smith" wrote:

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Hi Steven, Thanks for your involment and help. However it was my mistake when
I said about DAYS. Forget DAYS. I really wanted to count how many times a DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008), really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Really counting DATES
 

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Sure - If you examine Smith's formula, it count all occurances of 1 January
2008, regardless of format.

My formula make no assumption on the decade
1 january 1998
1 january 1988
will also be counted if they are in the column.

The sumproduct simply checks that the year ends in an 8.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any
B cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this dates
to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Really counting DATES
 

P.S.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in
any B cell
and then the same formula( of course not exactly "same" but alike)
will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this
dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Really counting DATES
 

Cool. Thanks.

"Gary''s Student" wrote:

Sure - If you examine Smith's formula, it count all occurances of 1 January
2008, regardless of format.

My formula make no assumption on the decade
1 january 1998
1 january 1988
will also be counted if they are in the column.

The sumproduct simply checks that the year ends in an 8.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

Hi, Gary, either your playing tricks with my mind or you answer to a
different post.
You really lost me with that formula. Could you please explain it to me,
don't forget
my XL "disabilities" and be gentle.


"Gary''s Student" wrote:

A beautiful question that I will use next semester!

Say we have in A1 thru A8:

1/1/2008
1/1/1998
1/1/1948
1/1/1945
1/1/2009
12/25/2006
1/18/2008
12/1/2008

then only the first three should count:

=sumproduct(--(month(a1:a100)=1),--(a1:a100<""),--(day(a1:a100)=1),--(--right(year(a1:a100),1)=8))

will return a three


However if the data in A1 thru A8 are Text rather than Date, the same
formula will work.
--
Gary''s Student - gsnu200827


"Excelfan" wrote:

The reason for the title is that I've been looking at all old posts with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any B cell
and then the same formula( of course not exactly "same" but alike) will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but for
reason
of space and difficulties of my worksheet I cannot translate this dates to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Nope.
It gives me zero at all times for some reason.

"T. Valko" wrote:

P.S.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in
any B cell
and then the same formula( of course not exactly "same" but alike)
will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this
dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1) for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps

Really counting DATES
 

I assume you got your answer from Biff, but if now, use:
=countif(a:a,date(2008,1,row(b1)))

and copy down.

Regards,
Fred.

"Excelfan" wrote in message
...
Thanks Fred, it works too.
Do you have an answer for my last post ( probably went before yours)
How can I copy down adding a day everytime?
ex your formula 2008,1,1
2008,1,2
2008,1,3 etc, so I dont have to write the formula
every date/day.

"Fred Smith" wrote:

Try it like this:

=countif(a:a,date(2008,1,1))

Regards,
Fred

"Excelfan" wrote in message
...
The reason for the title is that I've been looking at all old posts
with
this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time, or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in any
B
cell
and then the same formula( of course not exactly "same" but alike) will
count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ... but
for
reason
of space and difficulties of my worksheet I cannot translate this dates
to
days.
Thanks.
1/5/8
1/7/8

Really counting DATES
 

Hmmm...

If this works:

=COUNTIF(A$1:A$6000,DATE(2008,1,1))

Then this has to work:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

Because it's essentially the same as:

=COUNTIF(A$1:A$6000,DATE(2008,1,1))


--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Nope.
It gives me zero at all times for some reason.

"T. Valko" wrote:

P.S.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try it like this:

B1:

=COUNTIF(A$1:A$6000,DATE(2008,1,ROWS(B$1:B1)))

--
Biff
Microsoft Excel MVP


"Excelfan" wrote in message
...
Hi Steven, Thanks for your involment and help. However it was my
mistake
when
I said about DAYS. Forget DAYS. I really wanted to count how many
times a
DATE
is repeated and the formula works as : =COUNTIF(A1:A6000,"1/1/2008),
really
works for me.
Now in the belows cells of that formula ( formula in B1) I need to
copy
down
to
=COUNTIF(A1:A6000,"1/2/2008") in B2
=COUNTIF(A1:A6000,"1/3/2008") in B3

and so on without writting everytime the formula, something like "copy
down+1day"
kind of thing.
THANKS.

"Steven" wrote:

On Jan 18, 5:04 pm, Excelfan
wrote:
The reason for the title is that I've been looking at all old posts
with this
title and
not finding the answer I need, I'm sure is simple, but not for me.
I have a thousands of dates listed in column A.
Those dates could be found several times repeated or just one time,
or
none.
ex:
1/1/8
1/1/8
1/2/8
1/5/8
1/5/8
2/2/8
1/3/9...
The formula I'm looking for will count all dates listed as 1/1/8 in
any B cell
and then the same formula( of course not exactly "same" but alike)
will count
all dates listed as 1/5/8 in another B cell, and so on.
It will be like counting how many sundays, mondays, tuesdays, ...
but
for
reason
of space and difficulties of my worksheet I cannot translate this
dates to
days.
Thanks.
1/5/8
1/7/8

I understand that you want to count the number of times certain days
of the week are repeated in column A.
Firstly convert the dates to weekdays by adding the following
function
in Column B and dragging down =weekday(A1). This will return the
number of the day in the week e.g. Monday =2,Tuesday =3 etc..
Then to count the number of times that the days of the week
repeat ,insert the following function in column C =countif (B:B,1)
for
each day Sunday and then repeat for each day of the week. e.g.
=countif (B:B,=2) for Monday; =countif (B:B,=3) for Sunday etc..
Hope this helps