Calculate the amount of months between 2 dates in Salesforce via Formula

Hi:
Welcome back...I had a business scenario to where the business unit needed to calculate the amount of months between 2 dates via Formula in Salesforce.com. But when CloseDate is the first of the month then add a month to the formula but when the close date is not the first of the month then do not add the month in.
This was the criteria, for the business.

Pretty Simple. But a little complex.

So Let's go to the solution and let me break it down for you.
The object affected in this case is Opportunity.

The Fields affected are:
CloseDate <== Standard date field in Opportunity Object
Project_End_Date__c <== Custom Date Field in Opportunity Object
of_Months__c <== Formula Number Field in Opportunity Object

The formula is easy:
Before we get to the formula lets brain storm 3 possible scenarios:

Scenario 1:
If the CloseDate Day starts with 1 Lets Say 01/01/2011 and Project End Date is 3/01/2011 then the number of months should be 3.
This is the easiest scenario


Scenario 2:

If CloseDate is 01/11/2011 and Project End Date is 03/11/2011 then the number of months is 2.

Scenario 3:
If CloseDate is 01/11/2011 and Project End Date is 03/21/2011 then the number of months is 2.

 This formula takes the business criteria and calculates it:
The first criteria for the business was:
When CloseDate is the first of the month then add a month to the # of Months Field.
IF(DAY(CloseDate) = 1, <== If this is true Such as 02/01/2011

(((YEAR( Project_End_Date__c ) - YEAR( CloseDate)) *12) +
(MONTH( Project_End_Date__c ) - MONTH( CloseDate ) ) +1)

 The second criteria for the business was:
This is when the first criteria becomes false: IF(DAY(CloseDate) = 1,
Such as 02/11/2011

((YEAR( Project_End_Date__c ) - YEAR( CloseDate )) *12) + (MONTH(
Project_End_Date__c ) - MONTH( CloseDate ) ) )

So here is the formula:


IF(DAY(CloseDate) = 1, (((YEAR( Project_End_Date__c ) - YEAR( CloseDate
)) *12) + (MONTH( Project_End_Date__c ) - MONTH( CloseDate ) ) +1),
((YEAR( Project_End_Date__c ) - YEAR( CloseDate )) *12) + (MONTH(
Project_End_Date__c ) - MONTH( CloseDate ) ) )

Just a quick tip for formulas, I do it in Excel before I write it out on the salesforce.com field.
Check out my Other Blogs:

VisualForce Made Easy

Dataloader Made Easy

Regards,
Zishan