Revenue allocation between two dates

[mikedharding]

I am trying to allocate revenue on a contract between two dates but am struggling. I'm going back and forth using IF, AND, EOMONTH but just can't get the right formula. Help please! See attached

[mrice]

You might consider the YEARFRAC function which gives the fraction of the year between two dates. If you combine this with your contract size prorataed (?) for the entire year, you can do the calculation.

Paste

=MIN((YEARFRAC($A6,E$4) * $D6)/YEARFRAC($A6,$B6),$D6)

into E6 and copy across

[Pete_UK]

You can use this formula in E6:

=IF(AND($A6<=E$4,$B6>=EOMONTH(E$4,-1)+1),ROUND((MIN(E$4,$B6)-MAX(EOMONTH(E$4,-1),$A6))/($B6-$A6)*$D6,3),"")

then copy across and down as required. I found that if I used ...ROUND(… ,2)… it would be 1p out in the total for the first line, whereas this formula is only 1/10th of a penny out in the total.

Hope this helps.

Pete