difference between 2 dates- Calculation of Months, using EOMonth formula

[gopalakrishna rao]

Hi,
Excel Friends,

I want to get the difference between the two dates by taking the last date of the month for the purpose of calculation.
I am using the following formula but I am not getting the desired result, can any body help me to solve this problem.
=DATEDIF(EOMONTH(C2,0),(EOMONTH($G$1,0,"m")
IN C2 DATE IS 15-03-13 AND IN G1 COLUMN DATE IS 15-03-15, i want the completed months as 24, ( 13= 9 months, 14= 12 months, & 15= 3 months, totally 9+12+3).
Please help.
Advance thanks to the problem solver.
gopalakrishnarao

 

[Somendra Misra]

Hi Gopalhrishnarao,

Try =INT(((G1-C2)/365)*12)

Regards,

 

[gopalakrishna rao]

Thank you it is working. but where I went wrong? Please point out the mistake which I am doing.

 

[Somendra Misra]

Your formula =DATEDIF(EOMONTH(C2,0),(EOMONTH($G$1,0,"m") should be as below
=DATEDIF(EOMONTH(C2,0),EOMONTH($G$1,0),"m")

Regards,

 

[gopalakrishna rao]

Thank you for enriching me and for having pointed out where i was doing mistake. Now it is working fine.
Once again thank you.

 

[Somendra Misra]

Welcome back anytime.

Regards,

 

[gopalakrishna rao]

The first formula and second formula is showing the difference in months, average to EOmonth, what is the reason. I am herewith uploading the file, containing the formula side by side. Please explain where I went wrong. File is confidential. please delete after verification.

Edit: I had changed the file (@Somendra Misra )

 

[gopalakrishna rao]

please explain the difference in months. nearly two months difference is coming for the 1st and 2nd formula.

 

[NARAYANK991]

Hi Rao ,

Your formula in column I is inconsistent ; it has been copied down wrongly. Copy the formula which is in cell I3 downwards.

Secondly , there is no need to use a construct such as EOMONTH($F$2 , 0) since F2 already has the last date of the month. Thus your formulae :

=DATEDIF(EOMONTH(C3,0),EOMONTH($F$2,0),"m")

is the same as :

=DATEDIF(EOMONTH(C3,0),$F$2,"m")

Narayan

 

[Somendra Misra]

Thanks @NARAYANK991 Sir, for replying.

@gopalakrishna rao I hope now you understood the difference why the two formulas giving different value.

One thing I want to mention here the INT formula calculate the exact difference between two dates where as the DATEDIF formula you are constructing using last day of the month, so in some cases it may differ as EOMONTH will add certain number of days in the formula.

Regards,