Help with producing a 24-hour peaktime analysis in Excel

[martin]

Hope some excel champion can help me with the following dilemma:

I have data on burglaries that look like this, sorted by Burglary #
and then Start Date of when the burglary was committed:

Burglary # Start Date End Date Start Time End Time
1 01-Jan-01 01-Jan-01 21:00 21:45
2 01-Jan-01 01-Jan-01 21:20 23:45
3 01-Jan-01 02-Jan-01 22:00 00:45
4 01-Jan-01 08-Jan-01 22:05 22:05

I want to be able to:

(1) calculate the duration of time for each burglary in hours. So,
for Burglary #1 it would give a value of 45 minutes. For Burglary #4
it would give a value of 168 hours. How do I do this? I can see how
to do it if all burglaries start and end on the same date, but not
when the 'window' overlaps dates.

(2) construct a 24-hour timeline to show during which hours of the day
burglaries are occurring. it would be a peak time analysis.

the end product could look something like this, based on the data
above:

(22:00 - 22:59) 23:00 - 23:59) (00:00 - 00:59) (01:00 - 01:59) (02:00
- 02:59)
3 3 2 1 1

I would then plot the counts for each 1-hour windows as a bar chart.

But how do I deal with burglary #3? For #1 and #2 a straightforward
COUNTIF can be written, because they occur on the same date. For #4,
I just consider the burglary to have occurred throughout the entire
24-hour period, even if we suspect it didn't. But for burglary #3 the
time span is fairly short but crosses two dates, so that the end time
of 00:45 looks less than the start time of 22:00, even though it is
later in time.

Do I need to concatenate date and time somehow, so they're on a
continuum?

Any help gratefully appreciated.

Martin

 

[Laura Cook]

With data in columns A-E:

=((C2+E2)-(B2+D2))*24

Format as a number.