Months between two dates

[Howard Brazee]

What is the simplest way of determining the integer number of months
between two dates?

I would like to simplify my spreadsheet which has a hard coded date in
one cell, and the following in a column:

A22=TEXT(DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)),"MMMM YYYY")

I want M22 to have the number of months between A22 and M2.

Each year has 15 lines, including some headers, and when a new fiscal
year starts, I duplicate the previous one, making the spreadsheet
longer - with summary lines between the old months and the new months.


--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

 

[Patrick Molloy]

=month(m22)-month(A22)
or is that too obvious?

 

[Howard Brazee]

=month(m22)-month(A22)
or is that too obvious?

Actually, it's:
=MONTH($M$2)-MONTH(A22)

But when $M$2 is formatted as date, containing 11/21/1988
and when A22 is formatted as date containing
=TEXT(DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)),"MMMM YYYY")
displayed as September, 2008

My new cell (M22) displays 2, which is obviously wrong.
I need to do arithmetic with this value once I correct it, so I
created a test cell (M23) = M22*2, which displayed 1/4/1900

What I wanted is to have M22 to contain the number (not date) 239. (I
may be off a month here).

My solution may be to simplify A22, I don't know.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

 

[Howard Brazee]

Just a note: I tried:
=(YEAR(A21)-YEAR($M$2))*12+MONTH(A22)-MONTH($M$2)

displaying the number I wanted (238) in M22

I still don't know why my test of M23=m22 *2 displayed a date
(8/25/2000), I wanted it to display an integer. I had never used
that column before I did that test.

Actually, it's:
=MONTH($M$2)-MONTH(A22)

But when $M$2 is formatted as date, containing 11/21/1988
and when A22 is formatted as date containing
=TEXT(DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)),"MMMM YYYY")
displayed as September, 2008

My new cell (M22) displays 2, which is obviously wrong.
I need to do arithmetic with this value once I correct it, so I
created a test cell (M23) = M22*2, which displayed 1/4/1900

What I wanted is to have M22 to contain the number (not date) 239. (I
may be off a month here).

My solution may be to simplify A22, I don't know.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison

 

[Bernd P]

Hello James,

=DATEDIF(A1,A2,"m")
and apply general format.

Regards,
Bernd

 

[Howard Brazee]

Hello James,

=DATEDIF(A1,A2,"m")
and apply general format.

Regards,
Bernd

That's certainly simpler, thanks.

--
"In no part of the constitution is more wisdom to be found,
than in the clause which confides the question of war or peace
to the legislature, and not to the executive department."

- James Madison