# Calculate Years and Months difference - DateDiff and MOD issue

1. ## 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

2. ## 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

3. ## 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

4. ## Re: Calculate Years and Months difference - DateDiff and MOD issue

I am more of a formula guy than VBA, sorry

5. ## 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. ## Re: Calculate Years and Months difference - DateDiff and MOD issue

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

7. ## 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.

8. ## 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. ## Re: Calculate Years and Months difference - DateDiff and MOD issue

It's working partially...

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. ## Re: Calculate Years and Months difference - DateDiff and MOD issue

assumed start date is less than the end one

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