Detecting Overlapping Timeframes

[rosebowler2010]

I am looking for a way to visually detect overlapping timeframes. For instance on a shift change, when are we paying more than one employee for the same time? Below is an example of what the database looks like. A chart similar to a GANT chart would be useful but even conditional formatting would work. Any help would be appreciated.

<table border="0" cellpadding="0" cellspacing="0" width="529"><col style="width: 65pt;" width="87"> <col style="width: 95pt;" width="126"> <col style="width: 79pt;" width="105"> <col style="width: 86pt;" width="114"> <col style="width: 73pt;" width="97"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 65pt;" width="87" height="20">Date</td> <td class="xl65" style="border-left: medium none; width: 95pt;" width="126">Employee Name</td> <td class="xl64" style="border-left: medium none; width: 79pt;" width="105">Employee PIN</td> <td class="xl64" style="border-left: medium none; width: 86pt;" width="114">Shift Start Time</td> <td class="xl65" style="border-left: medium none; width: 73pt;" width="97">Shift End Time</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">1/1/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John Doe</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">1111</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">5:45AM</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">4:00PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">1/1/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jane Doe</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">2222</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">6:00AM</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">4:15PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">1/1/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jim Doe</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">3333</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">4:00PM</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">11:45PM</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">1/1/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Randy Doe</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">4444</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">3:45PM</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">11:45PM</td> </tr> </tbody></table>

 

[shg]

If you're using Excel 2007+, http://www.box.net/shared/97plvyn4ie may do what you need.

 

[capparfrank]

insert another column between shift start and shift end. Fill it with the difference (ie "ShiftEnd - ShiftStart) and this will be 'duration'.

Now you can just insert a stacked column chart (not the 100% variety). The first series is shift start, the second series is duration.

once the chart draws, edit shift start series and make it have no fill and no borders.

I'm not adept enough to paste good examples
but I hope that very brief explanation is helpful.