Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 14th 15, 11:40 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2015
Posts: 3
Default 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!

  #2   Report Post  
Old January 14th 15, 12:11 PM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default Need help on a potential gannt chart derivative

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
  #3   Report Post  
Old January 15th 15, 07:47 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2015
Posts: 3
Default

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.
  #4   Report Post  
Old January 15th 15, 11:39 AM posted to microsoft.public.excel.charting
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default Need help on a potential gannt chart derivative

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
  #5   Report Post  
Old January 15th 15, 01:08 PM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2015
Posts: 3
Default

[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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with dates in a Gannt chart - please help ricardomartinez Excel Discussion (Misc queries) 0 August 1st 12 04:34 PM
Derivative asunwatcher Excel Discussion (Misc queries) 1 September 23rd 09 04:17 PM
Gannt chart: formulas and conditional formatting Karl Excel Discussion (Misc queries) 3 August 13th 08 08:20 PM
how do i create gannt chart in excell? Deepak Charts and Charting in Excel 1 April 7th 06 12:39 PM
Formula How to add potential nos Wilie Loh Excel Worksheet Functions 2 March 19th 06 03:48 PM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017