+ Reply to Thread
Results 1 to 5 of 5

Need to calculate individual work experience in years and months within excel.

Hybrid View

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Stafford, VA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Need to calculate individual work experience in years and months within excel.

    Hello All,
    My name is will and I am losing my mind trying to figure this out. My company would like to keep track of every individuals years and months of experience dating back to when they first started, and it only wants to calculate relevant experience. On top of that, they want it to be self updating so that it is always current. Here is my issue, I have no idea what I am doing. While researching I found that if I use the DATEDIF formula it will calculate the period between two dates. This is good, however, look at this example:
    John Doe work at one company from 12/2003 until 08/2006 and his experience is valid. He also worked from 09/2006 until 11/2008 where his experience does not count towards his overall "relevant" experince. He then continued to work from 12/2008 until present where his experience is relevent.
    So, as you can see I need excel to calculate the years and months from 12/2003-Present, while omitting the dates where his experience is not relevent (09/2006-11/2008). I tried a work around which was to calculate the first employment history using DATEDIF and then do the third, however, when I try to add the two seperate results I get an error. Any help with this would be much appreciated. Attached is the example in excel.

    Thanks
    Will
    Example Work Experience.xlsx

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Need to calculate individual work experience in years and months within excel.

    I would split the experience into series of periods. Measure the span of each period in days. In a separate column, assign each period either an "R" or a "NR". You can then sum all the days for a given person that are "R" with a SUMIF() or SUMIFS() formula.

    Once you have relevant days, convert them to years and months
    Gary's Student

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need to calculate individual work experience in years and months within excel.

    we would be more than happy to help, but most members are disinclined to create your work for you. i would suggest that you at least make a start on your file and we can then help/advise on how to proceed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Need to calculate individual work experience in years and months within excel.

    I have attached a sample.
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Need to calculate individual work experience in years and months within excel.

    looks like you already have what you need there? i used a sumifs()0 to include the name as well though and came up with the same answer as you did...
    =SUMIFS($D$1:$D$20,$A$1:$A$20,A1,E1:E20,"R")/365.25

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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