Counting months 1

[Legends77]

Anyway to have 5/1/2010 minus 4/1/2010 = 1 (month)
I am trying to add a formula to automatically caclulate the number of months since the begining month and the current month.
This way, when I update the current month in the file, the # of months will automatically update.
I.E. when the cell is changed from 5/1 to 6/1, the number of months goes to 2.
The number of months feed many formulas and was hoping to further decrease file maintenance time.

 

[SkipVought]

Hi,

Month is an inexact period of time.

How do you want to calculate it? Please be very specific.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Legends77]

For this period it is being calculated by the change in month name. Essentially this is for something like a car note tracking sheet. I need to be able to tell how many payments were paid between the current month and the begining month. The number of payments is then automatically multiplied by the monthly payment to show how much has been paid.

 

[SkipVought]

faq68-5827

But you have not answered the exact question.

You can do arithmetic directly on dates. The difference will be in DAYS. So you get 27 days or 28 days or 30 days or 31 days. What do you want to happen?

Is a "month" based on 30 days? Or 1/12 of a 365 days? Or 28 days?

All af the above could be considered correct under different circunstances.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Legends77]

That is the difficult thing and guess there is no way to calculate what I need as I am going from Month change and not a physical date change. What I was looking for was May 2010 - April 2010 = 1 month. Appears only way I can do this is for me to make my own chart and relate April 2010 = 0 and May 2010 = 1 or somthing like that

 

[SkipVought]

Read the FAQ.

Your problem is that neither May 2010 nor April 2010 are REAL DATES. They are merely STRINGS! You cannot do calculations with strings.

You'll need to parse these strings to get MONTH and YEAR separately.

You'll need to convert the MONTH STRINGS (Jamuary, February, etc) to NUMERIC values (1, 2, etc) Set up a list and then use the MATCH funtion.

Then either generate real dates using the DATE function or do arithmetic using a combination of year and month numeric values.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If for ANY day in April and ANY day in May the result should be 1, use formula:
=12*(YEAR(date2)-YEAR(date1))+(MONTH(date2)-MONTH(date1))

combo

 

[Legends77]

Thank you! Works great!

 

[SkipVought]

Exactly WHAT is it that "works great?"

I'm sure that other browsers would like to know how you implemented your solution, as there are several ideas that have been posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Assuming that you do have real dates in your spreadsheet*, this should be a simple task**.

To clarify: you just want to count the number of calendar months between two dates, right?

Let's say that Column A contains the start date and Column B contains the current date - then you'd use this formula:
[tab][COLOR=blue white]=((YEAR(B2) - YEAR(A2)) * 12) + (MONTH(B2) - MONTH(A2))[/color]

Also, you wrote,

I.E. when the cell is changed from 5/1 to 6/1, the number of months goes to 2

There's no reason to manually change a cell to the current month.

[tab][COLOR=blue white]=Today()[/color]
is a dynamic formula that will automatically be updated to the current date each time you open the workbook. And all formulas that are based on it will be recalculated appropriately.


* Assumption based on the first sentence from your first post, "Anyway to have 5/1/2010 minus 4/1/2010 = 1 (month)"

** I mean no disrespect to SkipVought, who knows way more about Excel than I do, but I think he's over-complicating the issue.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Did something really strange just happen here? I could have sworn that nothing since Skip's post dated 24 May 10 11:22 was showing before I submitted my reply. And I didn't see a star awarded, either???

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John,

*Twilight Zone* ???

My over-labored point was to get the OP thinking that there may be more than one way of determining a difference of months and to make a conscious informed decision regarding the method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}