+ Reply to Thread
Results 1 to 10 of 10

Calculate Years and Months difference - DateDiff and MOD issue

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Calculate Years and Months difference - DateDiff and MOD issue

    Hi All,

    I have two dates in two columns and want to calculate their difference in Years and Months only.

    for example:
    in Column A - Date2 = 04/01/2014
    in Column B - Date1 = 02/16/2013

    their difference should be:
    in Column C (Date2 - Date1) = 1.1 (because only 1 year and 1 month is completed)
    in Column D (Today - Date1) = 0.9

    Which means about the format (Y.m), before decimal shows years and after it shows month difference.

    I have also tested with DateDiff function in VBA, but it everytime does not give the correct answers.

    Please find the attached file of my efforts and help me to solve this puzzle.

    With Regards,
    SunOffice
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    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
    52,917

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    Give this a try in C2, copied down...
    =IF(DATEDIF(A2,B2,"y")=0,"0 Years",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"0 months",DATEDIF(A2,B2,"ym")&" months ")

    (your date in B3 is the wrong way round)

    Also, the reason we ask for your location, is that some countries use different formats (dates etc) than others, and it helps to know that when we construct formulas
    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

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    Thank you FDibbins!!

    Yes your method works in column C by using formulas.

    I am facing issues with DateDiff and MOD functions in VBA, so looking for programming help. did you/anyone get time to see my codes (Alt+F11).

    Note: The defult date format is m/d/yyyy for me.

    With Regards,
    SunOffice
    Last edited by SunOffice; 12-08-2013 at 01:54 AM. Reason: describe the date date format as m/d/yyyy

  4. #4
    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
    52,917

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    I am more of a formula guy than VBA, sorry

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    It's really good to learn by you... Thank you!
    Only the thing is... my current requirements are actively looking for some VBA solutions.

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    Hi SunOffice,
    maybe something like
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    Thank you nilem for the efforts & writing!

    Yes.. your VBA approach is almost similiar to mine. If you have noticed that
    in Cell A2 - Start Date (Date1) = 11/30/2012
    in Cell B2 - End Date (Date2) = 12/08/2013

    here the 1 year and 1st/ one month will be completed on 12/30/2013, so the answer should be as 1 years 0 months and by our codes we are getting 1 Year(s) 1 month(s)
    I agree with FDibbins, but looking for a perfect VBA solution.

    btw nilem... is the operator "\" is similar to "/" for divide sign? I see... it's working in your codes.
    Last edited by SunOffice; 12-08-2013 at 03:15 AM.

  8. #8
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    interestingly
    if dt1 = 11/30/2013 and dt2 = 12/01/2013 then DateDiff("m", dt1, dt2, vbMonday) returns 1
    so try this
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    It's working partially...

    please see below example:
    in Cell A9: 12/08/2013
    in Cell B9: 12/01/2013
    so it gives a wrong answer for Cell C9 as 0 Year(s) -1 month(s).

    for this kind of case it should be as 0 Year(s) 0 month(s)

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Calculate Years and Months difference - DateDiff and MOD issue

    assumed start date is less than the end one

+ 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. Date difference in Years and months
    By xenos14 in forum Excel General
    Replies: 4
    Last Post: 11-07-2012, 08:48 AM
  2. Excel 2007 : Calculate years, months, day
    By helpme16 in forum Excel General
    Replies: 10
    Last Post: 01-18-2011, 12:34 AM
  3. Date issue - 5 years difference
    By ChrisNor in forum Excel General
    Replies: 1
    Last Post: 03-09-2009, 06:33 AM
  4. how to calculate years, months by using one formula
    By srinivasa rao in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-15-2005, 04:06 AM
  5. difference between two dates in years, months and days.
    By ruby in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2005, 12:06 PM

Tags for this Thread

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