+ Reply to Thread
Results 1 to 7 of 7

Need to total up multiple DATEDIF fields

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    3

    Red face Need to total up multiple DATEDIF fields

    HI there,

    I have been struggling with this formula. I have created a sheet where clients join a women's group and then either resign / move to another town. Each town has a women's group where they can then join again.

    I have the formula to work out how many years and months a person was at a particular area before they moved to another town or resigns. Formula: =IF(DATEDIF(AA5,AB5,"y")=0,"",DATEDIF(AA5,AB5,"y")&" years ")&IF(DATEDIF(AA5,AB5,"ym")=0,"",DATEDIF(AA5,AB5,"ym")&" months " - it works perfectly, showing the years and months based on the two dates provided.

    At the end of the row, I would like to calculate all the years and months combined as they receive a reward based on the amount of years they have been part of this women's group. How can I get the total years and months based on the various DATEDIFs? I have attached a document as an example.

    Thanx
    Attached Files Attached Files

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to total up multiple DATEDIF fields

    Just so I am sure..all the YEARS and MONTHS in the "term" ranges get added together to get the final value?
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    12-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    3

    Red face Re: Need to total up multiple DATEDIF fields

    Yes that's right!

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to total up multiple DATEDIF fields

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


    Hope this helps

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Need to total up multiple DATEDIF fields

    You could use SUMIF in the DATEDIF formula, e.g.

    =DATEDIF(SUMIF(G$4:AA$4,"From",G5:AA5),SUMIF(H$4:AB$4,"to",H5:AB5),"y")&" years "&DATEDIF(SUMIF(G$4:AA$4,"From",G5:AA5),SUMIF(H$4:AB$4,"to",H5:AB5),"ym")&" months"
    Audere est facere

  6. #6
    Registered User
    Join Date
    12-18-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Need to total up multiple DATEDIF fields

    It does, thank you so much!

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need to total up multiple DATEDIF fields

    You are very welcome
    Thank you for rep

    Please remember to mark the thread as solved if you are satisfied with your solution :
    To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

+ 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