Working with blocks of time

[DM]

Hello,

I have a datasheet that contains start times and end times
for incidents by day of the week. For example:
Mon: start = 8:30am end = 9:45am
start = 10:00am end = 12:00pm

So what I would like to do is take each 24 hr day and
break the times into 4 hour blocks. Then using a stacked
bar chart show the period of the day when the most
incidents occur. The chart would say something like this:
Mondays from 12:00am to 4:00am (5 incidents)
from 4:01am to 8:00am (3 incidents)
from 8:01am to 12:00noon (1 incident)

Is this possible?

Thank you

 

[Bernie Deitrick]

DM,

If your table of start times and end times is in A1:B10 (start time in A,
end time in B, with a label in row 1) then the array formula, entered with
Ctrl-Shift-Enter

=SUM(($A$2:$A$10<=C2)*($B$2:$B$10>C2)+($A$2:$A$10<=D2)*($A$2:$A$10>C2)*($B$2
:$B$10>D2))

where C2 is the start time of the block, and D2 is the end time of the
block. Note that inceidents that overlap time blocks will be counted as
happening in each - though the formula can be changed to change how the
counting is done.

Additional block times can be entered in C33 etc, and the formula can be
copied down to match.

HTH,
Bernie
MS Excel MVP

 

[DM]

Hmm...Just so I am clear and forgive me if I am not asking
the right question....
Is the result of this formula times broken into 4hr
blocks? When I put this into my pivot table, will it show
me that for Monday...from this time to that time etc?

Thank you so much for your time and help with this....

DM

 

[Bernie Deitrick]

DM

No, this won't work with a pivot table. You need to create your own table,
with two columns of start and end times, and the formulas.

HTH,
Bernie
MS Excel MVP

 

[DM]

I see....I am also going to need to ad the days of the
week and month..so this will be a manual table correct??

DM

 

[Bernie Deitrick]

DM,

You should try a different approach if you want your table to be automatic
(pivot table).

When you create your incident report, set it with columns like this

Incident# StartTime EndTime Day Week Month TimePer1
TimePer2 TimePer3

and fill out each row completely (Use True/False for the last three (or
more!) to assign an incident to a time block)

Then you will be able to use a pivot table to summarize Month, Week, Day,
and time periods.

HTH,
Bernie
MS Excel MVP

 

[DM]

Okay....that sounds good but then how can I use the data
sheet time info to tell me okay TimePer1 is equal to
12:00am to 4:00am?? Or can I even do this...

DM

 

[Bernie Deitrick]

DM,

Change the heading from TimePer1 to "12:00am to 4:00am" (and etc.). You can
use formulas to fill in those columns or manually enter the True/False
values. You haven't said where an incident that stretches from 7:00 AM until
1:00 PM should be placed, so I'm not going to figure out the formulas for
you until you say that.

HTH,
Bernie
MS Excel MVP

 

[DM]

Bascially,

The answer is that any incident can go over the 4 hour
block with respect to how long it stays open...so in that
case lets say for example....
Incident #1 start time = 12:00am endtime = 4:30am...to me
this would still be in the midnight to 4am and also in the
4:01am to 8:00am timeframe...

David