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!

Hi,

Am Wed, 14 Jan 2015 10:40:04 +0000 schrieb xshamirx:

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 !

please have a look:
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Gantt_Chart"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you so very very much. This was very helpful for the visualization.

I am going to try to come up with a way to calculate the overlapping years working across each person with each other. If you can help with that that would be great but if not I'll see what I can do by hand.

Thank you very much once again.

Hi,

Am Thu, 15 Jan 2015 06:47:31 +0000 schrieb xshamirx:

I am going to try to come up with a way to calculate the overlapping
years working across each person with each other. If you can help with
that that would be great but if not I'll see what I can do by hand.

have another look to OneDrive.
You have to download the file because macros are disabled in OneDrive.
Now there is an UDF in the file to calculate the overlapping years.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[i]
have another look to OneDrive.
You have to download the file because macros are disabled in OneDrive.
Now there is an UDF in the file to calculate the overlapping years.



Thank you so very much. This is EXACTLY what I was looking for.

Sincerely,
Shamir