Create a variable that calculates the number of months between a start and end date. You can use this for calculating a monthly average, or as a multiplier for projections.

1. Open your report and click Design on the menu bar in the upper-right corner to enter design mode.
2. If you're using dates from prompts:
   
   1. On the Reporting Elements tab, insert pre-defined cells with the date prompts in the header of the report.
   2. Open Notepad in the Windows Start menu. Copy and paste the formulas from the cells to the Notepad document.
3. Create a new variable in your report:
   
   1. On the Data Access menu, go to the Data Objects tab. From the New Variable drop-down list, select New Dimension.
      
   2. Enter the name for your new variable. Ensure your variable name is meaningful and tells you what the data is. You can change it later if you need to.
4. Select Dimension from the Qualification drop-down list.
5. Click the Formula tab and enter the formula you need.
   
   1. For YYYYMM formatted dates (Character):
      =MonthsBetween(toDate( copy your start date formula here;"yyyymm"); todate( copy your end date formula here;"yyyymm") )
   2. For MM/DD/YYYY formatted dates:
      =MonthsBetween( insert start date field here;insert end date field here)
      img title="Create New variable" src="/sys_attachment.do?sys_id=2b29aae6130b760012c2bc122244b0f0" alt="Formula Tab under Create Variable Options on Business Objects Webi" width="400" height="" />
6. Click the OK button to create the new variable.

Usage

This month count can be used to calculate the monthly average expense for a range of months returned by the data provider. It can also be used as a multiplier for calculating a projection if the end date is a point in the future.

Caveat

Business Objects calculates the return value for Monthsbetweeen (n) by:

1. Determining the number of days in the month of first_date.
2. Adding this number of days n times to first_date until the resulting date is the same as or later than last_date.
3. Returning n if the result is the same as the last_date, n-1 if later than last_date.

This means that if your date range goes from the end of a month to the end of the following month (in the same or any subsequent year as the year of the first month), and the following month has fewer days, Business Objects returns a value of n which is one month shorter than expected.