Dear all,
I have a spreadsheet which contains Revenue for 2014, Start Date and End Date. I have already worked out a formula to split amount in each month but I`m facing an issue allocating amount only to year 2014.
For example, if Revenue for 2014 is $12000, and if start date of contract is 1/1/2014 and end date of contract is 1/1/2017, it should only split amount in year 2014 (which is $ 1000 per month, and NOT 333.33 which is calculation for 3 years). I only need to report on year 2014 so Amount should only be divided for same year. Same goes if contract starts in 2012 or 2013, and end in 2014 or anytime after that. Formula should only calculate revenue for 2014.
I was able to work out two formulas but none of them appear to be helping me. Please help!
=IF(AND((D$1&" 1, "&YEAR($A2))+0>=$A2,(D$1&" 1, "&YEAR($A2))+0<=$B2),$C2/(MONTH($B2)-MONTH($A2)+1),0)
=(IF(AND($C2>=D$1,$B2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($C2,DATE(YEAR(D$1),MONTH(D$1)+1,1))-MAX($B2,D$1),0)/($C2-$B2))*$A2
Bookmarks