All geek questions in one place

Excel function that returns the difference between months?

I have a worksheet that has a start date and an end date. I need to get the number of months between these dates. I used the formula =MONTH(B1)-MONTH(A1) to get this #. However, this formula fails when my dates span several years — if the start date is 1/1/2014 and the end date is 1/1/2015, then it returns “1” when I want to get “12”.

Then I tried DATEDIF(A1,B1,"m") to get the number of months between dates, and that worked. However, my problem can be reduced to the following example:

Situation 1:

Start date: 1/5/2014

End date: 3/1/2014

"MonthDif": 2

DateDif: 1

Situation 2:

Start date: 1/5/2014

End date: 3/10/2014

"MonthDif": 2

DateDif: 2

As you can see, the DATEDIF function returns # full months, while my month - month function returns the number of months "occupied" by the difference between the start and end dates, regardless of whether they are full months.

I need the number of months, regardless of whether the months are full, for any period of time! Basically, I need what MONTH(B1)-MONTH(A1) returns, except that it will work for several years.

In addition, I was considering creating a custom VBA function to achieve the above. If anyone has any suggestions,

+5
source share
3 answers

If you use DATEDIF , but always DATEDIF from the 1st of the first month, you will get what you need, for example. if you use =A1-DAY(A1)+1 , which will give you the first of the month A1, so use this in DATEDIF like this

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

+5
source

Assuming B1 contains the end date, and A1 contains the start date,

 =IF(DAY(B1)>=DAY(A1),0,-1)+(YEAR(B1)-YEAR(A1)) *12+MONTH(B1)-MONTH(A1)
+2
source

I need what MONTH(B1)-MONTH(A1) returns, except that it will work for several years.

Well, well, at the risk of declaring the obvious:

 =MONTH(B1)-MONTH(A1) + 12*(YEAR(B1)-YEAR(A1))

Rationale: A year consists of twelve months.

0
source

Source: https://habr.com/ru/post/1207155/

More articles:


All Articles