+ Reply to Thread
Results 1 to 5 of 5

Dynamic calculation of length of service for various individuals using multiple columns

  1. #1
    Registered User
    Join Date
    01-24-2014
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2003
    Posts
    3

    Dynamic calculation of length of service for various individuals using multiple columns

    Hello. I have a large data set that looks like the spreadsheet shown below. I am interested in a formula that puts the correct value in the "Continuous Months on a Team" column using the person's name, team, and start/end months. The biggest challeng I am having is getting the correct output for is people similiar to Linda. Thank you in advance for the help!

    Person Team Month Start Month End Total Months Continuous Months on a Team
    Bob A 1 3 2 2
    Bob C 1 3 2
    Joe B 2 4 2 2
    Linda A 1 2 1 4
    Linda B 2 3 1
    Linda C 3 5 2
    Linda D 10 12 2 2
    Linda E 10 11 1
    Linda F 13 15 2 2
    Linda G 13 15 2

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic calculation of length of service for various individuals using multiple column

    Hi and welcome to the forum!

    Could you explain how you arrive at some of your desired results?

    For example, why does Bob get 2 for Continuous Months on a Team for Team A, though not the same for his 2 months with Team C?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    01-24-2014
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Dynamic calculation of length of service for various individuals using multiple column

    Thanks very much! Since Bob's months on Team A and C overlap, I would like to have one result that reflects 2 total months that he was on any team. Also, for Linda, she was on any team continually from month 1-5, 10-12, and 13-15. I would like the Continuous Months on Team column to therefore pick up 4, 2, and 2 for her.

    I have attached an image of the spreadsheet which should be easier to view.Example.jpg
    Last edited by jbmvabeach; 01-24-2014 at 02:08 PM.

  4. #4
    Registered User
    Join Date
    01-24-2014
    Location
    Virginia Beach
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Dynamic calculation of length of service for various individuals using multiple column

    I have been working to create unique identifiers associated with each person's name and beginnning/ending month date to try to solve this problem and I have had success getting rid of duplicate periods of time. However, I am unable to create a formula that would properly aggregate Linda's first 3 rows into a total of 4 months by dynamically leveraging the month start/end dates. Is such a calculation possible in Excel? Thanks

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic calculation of length of service for various individuals using multiple column

    And so the final column as you give it is what you would like to see? So you want to see three separate results for Linda?

    Is it possibie you could post an actual workbook (pictures are not very helpful on an Excel forum, unfortunately), with these and perhaps a few other examples included, preferably a few more where months for one person on different teams 'overlap', as you say, as I'm still not fully understanding how this will work in terms of your desired results.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple columns to individuals columns by sorted column
    By wildlifeduke in forum Excel General
    Replies: 4
    Last Post: 07-27-2012, 05:05 PM
  2. Excel 2007 : Length of service in age bands
    By SteveLaird in forum Excel General
    Replies: 0
    Last Post: 09-21-2011, 10:17 AM
  3. [SOLVED] Length of Service
    By Dom in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-17-2006, 05:55 PM
  4. Length of Service
    By Dom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2006, 03:26 PM
  5. Determine Length of Service
    By TheLeafs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2006, 03:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1