Detecting Overlapping Timeframes

rosebowler2010

New Member
Joined
Mar 8, 2011
Messages
1
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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

capparfrank

New Member
Joined
Feb 11, 2011
Messages
41
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.
 
Last edited:

Forum statistics

Threads
1,141,847
Messages
5,708,934
Members
421,599
Latest member
santosh234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top