LinkBack URL
About LinkBacks
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
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
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
Martin
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks