Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Months between two dates
Hello James,
=DATEDIF(A1,A2,"m") and apply general format. Regards, Bernd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Months between two dates | Excel Discussion (Misc queries) | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
months between 2 dates!!! | Excel Discussion (Misc queries) | |||
Months between two dates | Excel Programming | |||
Dates to months and calculating values for their months | Excel Programming |