# Spreading an Amount Over Time with Start and End Dates

The following shows various formulas that can be used to spread a total amount from a start date to an end date on a modeled sheet. If no end date is provided, or it is prior to the start date, this example will assume a duration of 1 year (365 days, 12 months) and will ignore the day portion of the start date (treating the start month as a full month). There is a series of accounts used to generate the result and there are two options for the result.

Option 1, or Per Day

- Works out the number of days between the start and end date then breaks the total value down to a per day value
- Then spreads from the start to the end date based on the number of days in the month.
- Months with more days will receive a larger share of the spread
- Mid month start/end dates will calculate correctly

Option 2, or Per Month

- Works out the number of months between the start and end dates, then breaks the total value down to a per month value
- Then spreads from the start to the end date with each month treated equally
- Mid months start/end dates work as expected unless they are in February of a leap year, then total amount will be slightly off.

The following Modeled sheet is the basis for the examples:

There are 3 columns that will be referenced in formulas:

- ROW.Value
- ROW.Start_Date
- ROW.End_Date

This is a basic example that can easily be expanded on by adding dimensions. The example sheet is included in this article so that you can optionally import it into your model to view it in action.

Steps for importing a modeled sheet

- Extract the XML file from the attached ZIP file Formula_Samples.zip
- Navigate to Modeling -> Level Assigned Sheets (or User Assigned Sheets)
- Click Upload New Modeled Sheet
- Browse for the file and click Upload (this will create a sheet called Formula Samples)

Below are the accounts, their description, and their formulas:

**Total Value**

Description: A redundant account as you could pull the value into other formulas right from the column in the sheet but it is common to pull it into an account so that is can be seen in reports and row details.

Formula:

ROW.Value

Result:

**Month Fraction**

Description: This account will be used to both determine the months that will have data based on the start and end dates, and weight the first and last month if they don’t start on the 1^{st} of the month.

Formula:

iff(

# Check if the end date was provided and after start, otherwise assume 1 year

isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date ,

iff(

this.version.positionof(this.month) >= this.version.positionof(ROW.Start_Date.month) and this.version.positionof(this.month) <= (this.version.positionof(ROW.Start_Date.month) + 11),

1, #ignore the day part of the date and use Start month to Start month + 11

0

),

# Start and End dates provided

timefraction(ROW.Start_Date, ROW.End_Date)

)

Result:

**Number of Days**

Description: This account is used for Option 1. It works out the number of days between the start and end dates or returns 365 if there is no end date, or the end date is before the start date.

Formula:

iff(

# if the end date is blank or before the start date assume 1 year

isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date,

365,

ROW.End_Date - ROW.Start_Date + 1

)

Result:

**Number of Months**

Description: This account is used for Option 2. It works out the number of months between the start and end dates by summing the Month Fraction determined above (you may need to adjust the time filter if your spread is greater than the 5 years allotted for). It returns 12 if there is no end date, or the end date is before the start date.

Formula:

iff(

# if the end date is blank or before the start date assume 1 year

isblank(ROW.End_Date) or ROW.End_Date < ROW.Start_Date,

12,

# Sum up the Month Fraction to get accurate base for Months

ROW.Month_Fraction[time=this.year-2:this.year+3]

)

Result:

**Value Per Day**

Description: This account is used for Option 1. It divides the Total Value by the Number of Days to get to a per day value.

Formula:

divf(ROW.Total_Value, ROW.Number_of_Days)

Result:

**Value Per Month**

Description: This account is used for Option 2. It divides the Total Value by the Number of Months to get a per month value.

Formula:

divf(ROW.Total_Value, ROW.Number_of_Months)

Result:

**Monthly Value From Per Day** (Option 1)

Description: This account will take the Value Per Day times the number of days in the month then times the Month Fraction to get to a value for each month from the start to the end date.

Formula:

# Use the daily value times the number of days in the month weighted by the month fraction

ROW.Value_Per_Day * this.month.numberofdays * ROW.Month_Fraction

Result:

**Monthly Value From Per Month** (Option 2)

Description: This account will take the Value Per Month times the Month Fraction to get to a value for each month from the start to the end date.

Formula:

ROW.Value_Per_Month * ROW.Month_Fraction

Result: