Normaliziing date and counting # of of occurances

[Jeff]

II am trying to review a log and determine how many events occurred on a
specific day.

There are 1000+ entries so I am trying to automate the counting.

the date format I as starting with is
5/22/2008 11:53

I am normalizing this using the following formula
=CEILING(R3,1)-1

Where R is the column that the date resides in.
This give me a result of 5/22/08

I then want to total for the different dates.

I have tried countif, sumif, and sumproduct but it does not seem to want to
count the data

=countif(Data_Range,A1) where A1=5/22/08
=sumif(Data_Range,A1,Data_Range) where A1=5/22/08
=sumproduct((Count=1)*(Data_Range=A1)) where A1=5/22/08

not sure if I am looking at a formula issue or a data issue. Am I having
problems because 5/22/08 is not really 5/22/08 but some thing else or
something else.

Any help would be appreciated. otherwise I have to count by hand.


Thanks

Jeff

 

[bpeltzer]

If Data_Range contains the 'unnormalized' entries, then try
=COUNTIF(Data_Range,">=" & A1)-COUNTIF(Data_Range,">=" & A1+1)
This counts the number of entries that are on or after the date specified in
A1, then subtracts the number that are on or after the following date.
What's left is those that are on the date specified, regardless of what time
on that day.
What's not clear to me in your original approach is where your 'normalized'
data is being placed; does 'Data_Range' refer to the original data or the
normalized data?

 

[David Biddulph]

Also, why =CEILING(R3,1)-1 and not just =INT(R3) ? ... unless you
particularly want a value of just 5/22/2008 in R3 to return 5/21/2008 ?