Months between two dates
 

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)),"MM MM 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

Months between two dates
 

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



"Howard Brazee" wrote:

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)),"MM MM 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

Months between two dates
 

On Fri, 25 Sep 2009 07:15:01 -0700, Patrick Molloy
wrote:

=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

Months between two dates
 

On Fri, 25 Sep 2009 08:34:14 -0600, Howard Brazee
wrote:

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.


On Fri, 25 Sep 2009 07:15:01 -0700, Patrick Molloy
wrote:

=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)),"MM MM 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

Months between two dates
 

Hello James,

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

Regards,
Bernd

Months between two dates
 

On Fri, 25 Sep 2009 08:08:48 -0700 (PDT), Bernd P
wrote:

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