tally analysis of burglary "time windows" (when times overlap days)

[martin]

I have to undertake the following task and am really struggling. Hope
someone can help.

My task is to identify the peak times within the 24-day when
burglaries are occurring within the police force area I work for.

I have the following variables for each of the 9,000 burglaries:

Start Date, End Date, Start Time, End Time

Example:
25/03/2003, 25/03/2003, 20:45, 21:30

What I want to achieve is a tally of burglaries occurring by 1-hour
windows of time within the 24-day e.g. the number starting (or in
progress) through 00:00 to 0:59, 01:00 to 01:59, 02:00 to 02:59 etc.
I plan to graph the tally counts.

BUT: how do I deal with this very common scenario, given the subject
matter:

Start Date, End Date, Start Time, End Time
25/03/2003, 26/03/2003, 23:45, 02:00

We do not know precisely when the burglary occurred, we just have a
window of time. This is common because victims don't often discover
their burglary for some time after it was committed, especially at
weekends and during vacations. So, many of windows of time span more
than one day (i.e. they are overnight burglaries).

I understand the principle behind the function:
=COUNTIF(Start Time,"<01:00")
for dealing with burglaries that were occurring (i.e. starting)
between 00:00 and 00:59

But I don't know how to include them again in situations when they
started before 00:00 but ran through to something past midnight. So,
a burglary starting at 23:45 on Saturday and finishing at 02:00 on
Sunday, I want to tally this kind of burglary to four windows e.g.
2300-23:59, 00:00-00:59, 01:00-01:59 and 02:00-02:59

Can anyone offer any suggestions regaridng formatting/formulas etc.?

Much appreciated.
Martin

 

[Excel Guru]

Buy Excel 2002 Formulas by John Walkenbach, excellent
publication.

Hope this helps Martin.

 

[Harlan Grove]

My task is to identify the peak times within the 24-day when
burglaries are occurring within the police force area I work for.

I have the following variables for each of the 9,000 burglaries:

Start Date, End Date, Start Time, End Time

Example:
25/03/2003, 25/03/2003, 20:45, 21:30

What I want to achieve is a tally of burglaries occurring by 1-hour
windows of time within the 24-day e.g. the number starting (or in
progress) through 00:00 to 0:59, 01:00 to 01:59, 02:00 to 02:59 etc.
I plan to graph the tally counts.

Within the 24-HOUR-day?

Given your data and 1 hour granularity, easiest to add 24 columns, one for
each hour of the day. Then use formulas to give 1s if the times in the
record overlap with the given hour and 0s if not. For the record above,
you'd want 1s in 20-21 and 21-22. You could get those as follows. I'll
assume your data records span 4 columns beginning in cell A3 (with column
headers in row 1, and row 2 blank for now), so columns A through D. Each
hour interval would appear in columns E through AB in row 1, and the labels
would be the beginning hour of the interval, so 0 for midnight, 1 for one
hour past midnight, . . ., 12 for noon, 13 for one hour past noon, etc.

Enter the following formula in cell E3.

=IF($C3<$D3,IF(MEDIAN(HOUR($C3),HOUR($D3),E$1)=E$1,1,0),
IF(MEDIAN(HOUR($C3)-1,HOUR($D3)+1,E$1)<>E$1,1,0))

Fill E3 into F3:AB3. Then fill E3:AB3 down as far as needed to provide
formulas for all your records.

Note: this throws out whole day intervals, which provide no useful
information with respect to time of day, so a record like

20/08/2003, 22/08/2003, 18:30, 10:15

would provide 1s in 18-23 and 0-10, and 0s in 11-17.

Now enter column sum formuls in E2:AB2, so if there were records in rows 3
through 9002, enter the following formula in E2.

=SUM(E3:E9002)

Fill E2 into F2:AB2. Now graph E1:AB2 as an x-y scatter chart.

 

[Andy Brown]

Enter the following formula in cell E3.

=IF($C3<$D3,IF(MEDIAN(HOUR($C3),HOUR($D3),E$1)=E$1,1,0),
IF(MEDIAN(HOUR($C3)-1,HOUR($D3)+1,E$1)<>E$1,1,0))
Excellent.

whole day intervals ... provide no useful information

Agreed, except ...

Note: this throws out whole day intervals ...
20/08/2003, 22/08/2003, 18:30, 10:15 ...
would provide 1s in 18-23 and 0-10, and 0s in 11-17.

ie: retains "valid" part-day intervals of more-than-whole-day events. My gut
feeling is that's blurring. How about a flag end column, ((end_date +
end_time)-(start_date + start_time)), and filter or sort out anything >1?

Rgds,
Andy

 

[Harlan Grove]

...
...

ie: retains "valid" part-day intervals of more-than-whole-day events. My gut
feeling is that's blurring. How about a flag end column, ((end_date +
end_time)-(start_date + start_time)), and filter or sort out anything >1?

It's a trade-off - potential information in intervals > 1 day vs indeterminacy
due to the possibility such burglaries happened any time during that day.

 

[martin]

Given your data and 1 hour granularity, easiest to add 24 columns, one for
each hour of the day. Then use formulas to give 1s if the times in the
record overlap with the given hour and 0s if not. For the record above,
you'd want 1s in 20-21 and 21-22. You could get those as follows. I'll
assume your data records span 4 columns beginning in cell A3 (with column
headers in row 1, and row 2 blank for now), so columns A through D. Each
hour interval would appear in columns E through AB in row 1, and the labels
would be the beginning hour of the interval, so 0 for midnight, 1 for one
hour past midnight, . . ., 12 for noon, 13 for one hour past noon, etc.

Hi Harlan,

Many Thanks. I had set about doing this already. It's the most
logical layout.

Enter the following formula in cell E3.

=IF($C3<$D3,IF(MEDIAN(HOUR($C3),HOUR($D3),E$1)=E$1,1,0),
IF(MEDIAN(HOUR($C3)-1,HOUR($D3)+1,E$1)<>E$1,1,0))

Fill E3 into F3:AB3. Then fill E3:AB3 down as far as needed to provide
formulas for all your records.

I try this, but it doesn't produce the correct results. Timespans of
2 hours appear to run for each 24x1hr window, and some burglaries
appear to have no time window at all (zeros). I'm not sure if the
time and date values are correctly formatted. Is it possible to
concatenate two separate time and date variables into one discrete
variable, against which absolute "earlier" and "later" date analysis
can be done?

Thanks for your help. I will play around a bit more, but will
probably end up going on to SPSS.

To Andy - Many Thanks. I'm going on an i2 training course in the
first week of September so I'll find out if i2 have a software
solution for this sort of routine.

Also, Excel Guru - I will ask my Dep't to consider getting that book.
At 800+ pages I'm sure a solution lies therein.

Cheers everyone.

Martin

 

[Andy Brown]

Hi Harlan ...

I try this, but it doesn't produce the correct results.

No, it's good. I'll send you a small sample file based on Harlan's solution.

Rgds,
Andy

 

[Harlan Grove]

...
I try this, but it doesn't produce the correct results. Timespans of
2 hours appear to run for each 24x1hr window, and some burglaries
appear to have no time window at all (zeros). I'm not sure if the
time and date values are correctly formatted. Is it possible to
concatenate two separate time and date variables into one discrete
variable, against which absolute "earlier" and "later" date analysis
can be done?

...

I should have known I'd get into trouble using MEDIAN. Replace the formula above
with the more basic (but more robust)

=--IF($C3<>$D3,AND(HOUR($C3+0)<=E$1,E$1<=HOUR($D3+0))=($D3-$C3>=0),$A3<$B3)