Month Calculation Error

Question

I am calculating no of months using "=IFERROR(MONTH(AJ1315-AI1315),0)" formula. AI is the end date and AJ is the start date. It gives me right answer for all months except for July. Instead of showing 6 from July to December, it is showing 7 months.

Answer 1

It's important that you understand why that happens here. When you have a start data of July 1 and an end date of Dec 31, then End minus Start will result in the number 183. Then =MONTH(183) will indeed returns 7, because it will return the month number of the 183rd day in Excel's internal calendar, being July 1, 1900.

If you always want to calculate the number of months within the same calendar year, and from the first day of one month to the last day of another, Vijay's formula will work. but it doesn't when you cross the calendar year end. Try and you will see.

In Excel you can use the DATEDIF function ( https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c ). But this a tricky one as it may produce incorrect answers as it works with whole months and whole years. You'll have to understand what it's doing and how to work with it.

Answer 2

Could you share us some data and expected result?

Try this one.

=IFERROR(DATEDIF(AI1315, AJ1315, "m"),0)+1

DATEDIF function - Microsoft Support

Answer 3

Use following formula

=IFERROR(MONTH(AJ1315)-MONTH(AI1315)+1,0)