Calculate # of months between two dates

[megera716]

I know this is such an easy one, but I'm not getting the desired result and there's a lot of complicated formulas out there that are throwing me off!

I just want to calculate the difference between month numbers. So April (4) and October (10) is 6. September (9) and October (10) is 1. December (12) and January (1) is also 1 but my formula:
=MONTH(AC1063)-MONTH(AP1063) returned -11.

I don't care what the day of the date is -- even if it's 12/29/19 and 1/2/2020, I want it to return 1.

 

[DanteAmor]

Try this

=DATEDIF(Start_date,End_date,"m")


Check this link:
https://support.office.com/en-us/article/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

 

[Tetra201]

See if the following formula works for you:

=MOD(MONTH(AC1063)-MONTH(AP1063),12)

 

[megera716]

I was using that first but it doesn't work if it's not a full month.

 

[megera716]

This works! Thank you Tetra!!

 

[Tetra201]

You are welcome.

 

[DanteAmor]

The formula is longer, but it works for dates with a difference of more than 11 months.


=DATEDIF(DATE(YEAR(AP1063),MONTH(AP1063),1),DATE(YEAR(AC1063),MONTH(AC1063),1),"m")


For example:

12/29/2019 and 12/2/2020
=MOD(MONTH(AC1063)-MONTH(AP1063),12)
Return 0


12/29/2019 and 12/2/2020
=DATEDIF(DATE(YEAR(AP1063),MONTH(AP1063),1),DATE(YEAR(AC1063),MONTH(AC1063),1),"m")
Return 12

 

[Tetra201]

If the OP needs to cover multi-year spans, there is a shorter formula, with fewer function calls and reference calls:

=DATEDIF(EOMONTH(AP1063,-1)+1,EOMONTH(AC1063,-1)+1,"m")

 

[DanteAmor]

Good option to find the first day of the month, I missed a little, but works.

From the first example the OP set dates for different years. And it is not multi-year, 12 months is still a single year.