Dave simplified Mikey's question so it's not so open-ended. Let me restate it. We know when a contract begins and ends. There are rules for deciding when the Revenue Phase starts and ends. We want to find two things:

- How many months are there in the revenue phase, and
- How much revenue occurs in 2016.

I'm going to find the revenue that occurs in the same year as the start, whatever year that may be.

I've defined names for the following cells. `Contract.value`

is defined as 1000.

```
Start $C$3
Stop $D$3
Months $E$3
```

`Start`

and `Stop`

are computed from the contract begin and end dates. We need the number of months from `Start`

to `Stop`

so we can find the "equal revenue phasing." It's really easy. Use DATEDIF or check How to calculate the number of months between two dates in Excel for an alternative method. Here's the formula for cell `E3`

.

```
=DATEDIF(Start,Stop,"M")+1
```

The `+1`

makes the number of months inclusive of the first and last months. In the example, there are 9 revenue months from July 2016 to March 2017.

Here's how to find the revenue in 2016, the year that the Revenue Phase starts.

First, find the number of months in 2016. Cell `C2`

computes it:

```
=IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1
```

Compute the number of months times the revenue for one month in the Revenue Phase. Cell `D2`

does it:

```
=C2*Contract.value/Months
```

Combining `C2`

and `D2`

into a single formula shows the same result in cell `E2`

.

```
=(IF(YEAR(Stop)>YEAR(Start),12,MONTH(Stop))-MONTH(Start)+1)*Contract.value/Months
```

There are some disadvantages to this approach. What if you want to find the revenue in 2017? The formula for 2017 follows the same pattern as `C2`

, but it's not the same. Things get messy if the Revenue Phase spans more than two years.

The problem can be generalized by making a simplified amortization table and using PivotTable to summarize it. `Start`

, `Stop`

and `Months`

are all that's needed to make a list of months and revenue. The PivotTable finds the revenue for every year in the Revenue Phase.

Row `2`

is special.

Here are the formulas for `A2:C2`

.

```
=Start
=YEAR(A2)
=Contract.value/Months
```

Use the formulas for `A3:C3`

to `Fill Down`

to fill the list. Fill the number of rows to allow for the largest possible months in the Revenue Phase. It's easy to add more.

```
=IF(A2<Stop,DATE(YEAR(A2),MONTH(A2)+1,1),"")
=IF(A3<>"",YEAR(A3),"")
=IF(A3<>"",Contract.value/Months,"")
```

The "trick" is in `A3`

. When the date in column `A`

for the previous row is after `Stop`

, the cell in column `A`

is blank and every following row is blank, so there will be one row for each month in the Revenue Phase -- no more, no less. The trick is useful when you want to make a list, but you don't know the number of rows in advance.

To make the pivot table, select the entire list, including blank rows. By selecting the entire list, the pivot table will still work if even when you change the contract dates. Use the `Year`

field for the pivot table row, and `Sum of Revenue`

and `Count of Year`

for the summary fields.