There is no built in function in Excel to calculate the number of months between 2 dates. DATEDIF (opens in new tab) does an ok job but the day part of the date can cause problems and is confusing. So a solution is to use some simple maths and Excel’s YEAR and MONTH functions (learn about the date functions in our Online Excel Intermediate Course )
Convert each date into a number of months
The first thing to do is convert each date into a total number of months, ignoring the day of the month. This can be done with a formula like
= YEAR( Date1 )*12 + MONTH( Date1 )
So a date like 25 January 2025 will become
= 2025*12 + 1
= 24 301 months.
Calculate the number of months between 2 dates
If you do the same thing with the 2nd date e.g. 5 September 1973 you get
= 1973*12 + 9
= 23 685 months.
Now it is simple to say 24 301 months minus 23 685 month is 616 months. Below a simple extract.
Add/ Subtract 1 month
You just now need to decide whether you need to add/subtract a month or not depending on what you are trying to calculate. This is similar to the issue with number of days between 2 dates being 1 day wrong (opens in new tab).
So if you want the number of months between 15 Dec 2024 and 1 June 2024 (I say this is 6 months) it would be
= YEAR(15 Dec 2024)*12 + MONTH(15 Dec 2024) – YEAR(1 June 2024)*12 + MONTH(1 June 2024)
= (2024*12 + 12) – (2024*12 + 6)
= 24 300 – 24 294
= 6 months
This seems right to me but perhaps your calc assumes end of the first month till the start of the 2nd month so only 5 months. Or perhaps it is the beginning of the 1st month to the end of the 2nd month which would be 7 months.
It is important to make sure you are happy with the result. Same applies to days. You sometimes need to add/ subtract 1 day to get the answer you want.