0

Excel’s DATEDIF function (with the option to return the number of months) returns different values for two pairs of date values even though the dates are in the same months.
In column C, I use =IF(A1="","-",DATEDIF($A1,$B1,"m")) + 1

    A            B         C
10-Nov-17    9-May-21     42
10-Nov-17   10-May-21     43

My question:
How to make the C2 return 42?
How to make column C return 42 without using +1 in the end of the formula?

3

If both date cells are the same date, the result is 1. The result turns over to 2 on the exact same day of the following month. So, 10-nov to 9-dec returns 1, 10-nov to 10-dec returns 2, not 1. The same principle applies to your sample data.

Row 1 is the 10th to the 9th and returns 42, but row 2 is the 10th to the 10th, so rolls over to the next number, which is 43.

This is how DateDiff works.

If you want to change the value that is returned, you will have to adjust the formula to your personal rules, like you did by adding 1.

2

If what you want to do is count the calendar months between the two dates, you can do that by changing each date to the first day of the respective month. To do that, subtract the day of the month from the date and then add 1.

So the formula becomes

=IF($A1="","-",DATEDIF($A1-DAY($A1)+1,$B1-DAY($B1)+1,"m"))
0

According to this site, I should change my formula to
=IF(A1="","-",DATEDIF($A1,$B1+15,"m"))

Rounding results up or down

  1. The DATEDIF() function always rounds down (by default) to the nearest >whole month or year.

  2. If you want to calculate months or years rounded up, add half a month or >half a year to the formula like this: =DATEDIF(A19,B19+15,”m”) for half a >month (or 15 days) or =DATEDIF(A21,B21+183,”y”) for half a year (or 183 >days). The result is now rounded up to the nearest month or year.

0

I have two dates for which I need to find number of months between them:

  • Date 1 (in cell B3): 04/25/2020
  • Date 2 (in cell C12): 04/02/2024

So according to the above example I should get the result as 48.

But, if I use the suggested formula (marked as answer, that suggests adding 15 days), it still gives me 47, because even if I add 15 to 02 (i.e. day of C12) it is still less than 25 (i.e. day of B3).

So I used the below formula to get the correct result:

=IF(B3="","-",IF(DAY(B3)<=DAY(C12),DATEDIF(B3,C12,"m")+1),DATEDIF(B3,DATE(YEAR(C12),MONTH(C12),DAY(B3)),"m")))

So what I am doing above is, if day of B3 is greater than day of C12, I am setting day of C12 same as B3.

Do let me know if this helps or there is anything more to this.

Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.