+ Reply to Thread
Results 1 to 8 of 8

Sum up series of Years, Months based on name

  1. #1
    Registered User
    Join Date
    07-17-2020
    Location
    London
    MS-Off Ver
    10
    Posts
    2

    Sum up series of Years, Months based on name

    Hello,

    I am benchmarking each team members' total years and months of experience and need to sum up series of years, months.

    (Cell B4) Previous years of experience prior joining : 14 years 9 months
    (Cell D4) Length of service : 0 years, 11 months

    So far i cannot find the formula to add them together. Any recommendation?

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sum up series of Years, Months based on name

    Hi, and welcome to the forum.

    I think you're complicating this unnecessarily by holding stuff like dates or periods as strings of text. You can't for instance use date arithmetic to derive other statistics.

    However here's one way in E4

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See attached.
    Attached Files Attached Files
    Last edited by Richard Buttrey; 07-17-2020 at 06:57 AM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Sum up series of Years, Months based on name

    alternatively
    =LEFT(B4,SEARCH(" ",B4,1)-1)+LEFT(D4,SEARCH(" ",D4,1)-1)+INT((MID(B4,SEARCH("m",B4,1)-3,2)+MID(D4,SEARCH("m",D4,1)-3,2))/12)&" years "&MOD((MID(B4,SEARCH("m",B4,1)-3,2)+MID(D4,SEARCH("m",D4,1)-3,2)),12)& " months"
    but I fully endorse Richards comments about storing numbers (dates) as text. it makes things unduly complicated

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Sum up series of Years, Months based on name

    Welcome to the forum.

    Maybe this?

    =DATEDIF(EDATE(C4,-(LEFT(B4,FIND("year",B4)-2)*12)-TRIM(MID(B4,FIND("month",B4)-3,2))),C4+DATEDIF(C4,$D$1,"d"),"y")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    07-17-2020
    Location
    London
    MS-Off Ver
    10
    Posts
    2

    Re: Sum up series of Years, Months based on name

    Thank you all for your support and the warm welcome to the forum.

    In my original spreadsheet I had formula for columns B and D but i had to convert data in column B in text to keep the spreadsheet under 1,000 KB requirement. Happy to share the excel spreadsheet in a PM if you have suggestions on how to keep the original formula.

    To calculate the previous years of experience prior joining for each employee. I calculate the difference in days between end date and the start date of each position a team member had prior joining, then sum up all the days and use the following formula to have a data presented in Year, Month =INT(SUM(E2:E6)/365.256363)&" years "&MOD(INT(SUM(E2:E6)/(365.256363/12)),12)&" months "
    i've tried to add the second part of the file.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Sum up series of Years, Months based on name

    Happy to share the excel spreadsheet in a PM if you have suggestions on how to keep the original formula.
    Not allowed, sorry, but one formula will not have made your workbook so 'heavy'!!!

    If you wanted us to work with existing formulae, you should have provided them at the start. If I have time, I'll have another look later.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Sum up series of Years, Months based on name

    Is this what you are looking for?

    =IF(COUNTIF(A$2:A2,A2)=1,INT(SUMIF($A$2:$A$22,A2,$E$2:$E$22)/365.256363)&" years "&MOD(INT(SUMIF($A$2:$A$22,A2,$E$2:$E$22)/(365.256363/12)),12)&" months ","")

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Sum up series of Years, Months based on name

    Quote Originally Posted by CCHAPO201 View Post
    Thank you all for your support and the warm welcome to the forum.

    In my original spreadsheet ....snipped

    Your original post was a much simplified example and didn't represent the size or layout of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    It is quite frustrating to work on a solution only to find you really meant something different. This just wastes everyone's time, time which could have been spent helping others.

    Please note for the future and be sure to be quite explicit and upload a truly representative workbook

+ 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. Adding Series of Years, Months, & Days
    By jmaitri in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-10-2019, 03:41 AM
  2. [SOLVED] COUNTIFS based on HIJRI&Gregorian Years and months
    By qadeerume in forum Excel General
    Replies: 6
    Last Post: 01-02-2017, 08:32 AM
  3. RD calculation based on No.of weeks,days,months,years
    By santbiju in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-05-2016, 05:38 PM
  4. Creating a time series from data grouped in months for multiple years
    By nzi0001 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2014, 03:21 PM
  5. [SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 04:20 PM
  6. days, months and years between todays date and a series of dates
    By jonathanpalmer in forum Excel General
    Replies: 4
    Last Post: 09-22-2010, 03:04 AM

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