Counting dates within a range
 

I have a large database of ~10,000 dated and timed entries. Each entry
represents the date and time of a hospital admission. I would like to know
for each entry, how many other events took place within 24 hours. I am trying
to figure out how many hospital admission happened in the 12 hours prior to
and after each admission to estimate the daily admission rate more accurately
than just by calendar day.

I have tried
=COUNTIFS(AV:AV,"<="&(AV2+0.5),AV:AV,"="&(AV2-0.5))
Where AV is the column of all dates, and AV2 is the date in question. This
formula is not accurate, and I can't figure out why

I'm looking to make a data sheet such as below (I've figured out the
calendar day column), but I can't figure out how to do the 24h count.

Id# Date Calendar day count 24h count
1 1/1/10 12:00 2 2
2 1/1/10 23:00 2 4
3 1/2/10 01:30 3 3
4 1/2/10 11:00 3 3
5 1/2/10 14:00 3 2

Thank you for your help!

Counting dates within a range
 

Try

=SUMPRODUCT(--(AV:AV<AV2+0.5),--(AV:AV=AV2-0.5))

--

HTH

Bob

"towem3" wrote in message
...
I have a large database of ~10,000 dated and timed entries. Each entry
represents the date and time of a hospital admission. I would like to know
for each entry, how many other events took place within 24 hours. I am
trying
to figure out how many hospital admission happened in the 12 hours prior
to
and after each admission to estimate the daily admission rate more
accurately
than just by calendar day.

I have tried
=COUNTIFS(AV:AV,"<="&(AV2+0.5),AV:AV,"="&(AV2-0.5))
Where AV is the column of all dates, and AV2 is the date in question. This
formula is not accurate, and I can't figure out why

I'm looking to make a data sheet such as below (I've figured out the
calendar day column), but I can't figure out how to do the 24h count.

Id# Date Calendar day count 24h count
1 1/1/10 12:00 2 2
2 1/1/10 23:00 2 4
3 1/2/10 01:30 3 3
4 1/2/10 11:00 3 3
5 1/2/10 14:00 3 2

Thank you for your help!