M
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
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