Need help on a potential gannt chart derivative
I'm currently working on my degree and I need to calculate overlapping time between multiple actors. The problem lies with both charting this data in excel and achieving some sort of visualization.
In column A I would have entries of people (say Ann, Bob, Chris, Doug, etc,)
The data I have include the year they started working and any break loner than one year between those dates:
Person ! end date ! break end ! break begins ! Start Date !
Ann ! 2015 ! no break ! no break ! 2011 !
Bob ! 2015 ! no break ! no break ! 2012 !
Chris ! 2015 ! 2012 ! 2011 ! 2008 !
Doug ! 2015 ! 2011 ! 2009 ! 2006 !
etc.
My problem lies with the fact that I need to be able to calculate how many years overlapping between each actor worked with another. (for example ann and bob worked together for 3 years while Ann and doug worked together for 4 years. Chris and Dough worked together four years (2008 to 2009, 2012 to 2013, 2013 to 2014 and 2014 to 2015). further more I need a way to account for multiple breaks. A bar chart of sorts (Gantt comes to mind) would be idea but I have no idea how to go about setting up the cells to give me these data (I imagine a matrix table would show me the overlapping years worked together but how to extract this has me confused)
Any help would be very much appreciated!
|