Here's another attempt using v9.3's Union capability.
I unioned the set with itself to get two copies of each entry.
The ClassHour calculated field only returns the second hour if needed:
IF CONTAINS([Table Name],"txt1")
THEN STR([StartHour])+":00 to "+STR([StartHour]+1)+":00"
ELSEIF [StartHour]<>[EndHour] AND [EndMin]<>0
THEN STR([StartHour]+1)+":00 to "+STR([StartHour]+2)+":00"
Not completely sure if this will play well with your utilization calculations,
but maybe it can be a start.
204024timeB.twbx 65.1 KB
You are right. I have not included all the results. The tricky part is the class starts at 539 and ends at 541. One minute of this class is in the 8:00 am to 8:59 am time slot (i.e. 540 - 539) and two minutes of this class is in the 9:00 am to 9:59 am time slot. So I need to somehow allocate 1 minute to the 8:00 am to 8:59 am time slot and 2 minutes to the 9:00 am to 9:59 am time slot for this one class.
Your idea of adding more columns may work. I can create multiple Calculated fields like "8 am to 8:59 am" to store the number of minutes that fall in this time slot. Then I have another Calculated field "Time Slot" to track which time slot it is falling into. For example, if a class meeting time is from 8:00 am to 10:20 am. The Calculated field values will be as follows.
Calculated Field name Calculated Field Value
"8 am to 8:59 am" 60 (in minutes)
"Time Slot" 8 am to 8:59 am
"9 am to 9:59 am" 60
"Time Slot" 9 am to 9:59 am
"10 am to 10:59 am" 20
"Time Slot" 10 am to 10:59 am
I sum all the duration in Calculated Field like "8 am to 8:59 am", "9 am to 9:59 am", "10 am to 10:59 am", etc. and store the result in another Calculated Field (e.g. Duration). Now, I have the "Time Slot" Calculated Field and the "Duration" Calculated Field. I put the "Time Slot" Calculated Field on Rows and "Duration" Calculated Field on Columns. Then it should show the following.
Time Slot Duration
8 am to 8:59 am 60
9 am to 9:59 am 60
10 am to 10:59 am 20
This is for one class section. If I apply this procedure to all class sections for all classrooms in one semester, I'll have the total number of minutes a classroom is occupied in a semester. I divided this number by the total available minutes. Then I get the overall classroom utilization per time slot in one semester. I have not tried this out yet but it seems like it will work. Will post the worksheet if it works.
Much time has passed, but I came across this other way to set things up.
This may no longer be useful for you now, but I thought I'd add this anyway.
I used the methods from here:
and made three sheets: your datatable, a list of all the possible minutes,
and a list of start and end time for the hour blocks.
All contain the same key, and they get joined together.
Sheet1 has your [Start] and [End]
Sheet2 has [Minutes]
Sheet3 has [Hour Start] and [Hour End]
Then you can filter just the minutes that were used by a particular class
[Minutes]>=[Start] AND [Minutes]<=[End]
and then group by hour blocks:
[Minutes]>=[Hour Start] AND [Minutes]<=[Hour End]
I didn't make the fractional use calculation, but you can
generate it by setting a 1 for each minute used and then adding it up.
There remains some trickiness with the start and end times of the classes.
The time 9:00 has to be only part of the 9:00-10:00 block
so the class that runs from 479 to 480 has to traverse two blocks.
204024hour.twbx 49.6 KB