Datedif returning incorrect result

[sharshra]

I'm using Datedif to calculate number of months between 2 dates. Can anyone advise how to fix this please?

Consider the following example -
H29 - 20-Jul-22
J29 - 19-Sep-22
Datedif -

=IFERROR(DATEDIF(H29,J29,"m"),"")

This returns 1 instead of 2.

 

[Dave Patton]

1 is correct.

T202210a.xlsm
	H	I	J
29	20-07-22	19-09-22	1
30	20-07-22	20-09-22	2
31
4d

Cell Formulas
Range	Formula
J29:J30	J29	=DATEDIF(H29,I29,"m")

 

[sharshra]

Thanks for the quick reply, Dave. In the following examples, I expect the difference to be returned as follows:

     20th Jul to 19th Sep - 2 months
     20th Jul to 20th Sep - 2 months, 1 day

If Datedif don't return these values, is there any other alternative to get these?

 

[Dave Patton]

You will have to explain the logic that you want to use.
What is a complete month?

 

[sharshra]

It's normal calculation for the month - One day prior to the current date in the next month. I think example makes it clear.

Example:
First date - 20th Jul 2022; second date - 19th Aug 2022; 1 month. Datedif shows it as 0 month, which is incorrect.

I'm sure there could be ways to calculate using multiple functions. For example, calculate number of days & divide by 30.

=(DATEDIF(P3,Q3,"d"))/30

But is there a better way? Is there any built in function that does this?

 

[bebo021999]

Add up 1 day into 2nd date may help:

=DATEDIF(P3,Q3+1,"m")

 

[sharshra]

Thanks, Bebo. I'm already using this. It still returns the month in integers. Wanted to know if there is a better way to get the months in fractions. For example, 1.25 months or 2.6 months.

 

[bebo021999]

How about:

=DATEDIF(P3,Q3+1,"m") + DATEDIF(P3,Q3+1,"md")/30

 

[etaf]

FYI - just be aware "md" may give errors - according to microsoft

DATEDIF function - Microsoft Support

Calculates the number of days, months, or years between two dates.

support.microsoft.com

Known issues​

The "MD" argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month, here is a workaround:

 

[sharshra]

Thanks, Bebo. I was using this combination of formulas to arrive at the expected result. But wanted to know if there is any function which can do this without having to combine different functions. Anyway, the combination works Marking it as solution.