Need Help Someone ! Revenue Recognition Evenly across the Months

[Surf1987]

Hi All,

Need: Allocating the amount of each item based on a monthly amount.

Amount Alloc Start Alloc End
$10,000.00 24-Jun-16 23-Jun-17
$10,000.00 24-Jul-16 23-Jul-17


Scenario 1 : - If month has 30 days Date Days Revenue
End Date of the month Allocate Start Date Year 30-Jun-16 7.00 $194
Start Date of the month Allocate End Date Year 1-Jun-17 23.00 $639


Scenario 2: - If month has 31 days Date Days Revenue
End Date of the month Allocate Start Date Year 31-Junl-16 .00 $215
Start Date of the month Allocate End Date Year 1-Jul-17 23.00 $618

[JohnTopley]

In G10

=IF(AND(MONTH(G$9)=MONTH($C10),YEAR($C10)=YEAR(G$9)),((EOMONTH(G$9,0)-$C10+1)/DAY(EOMONTH(G$9,0)))*$F$10,IF(MONTH(G$9)=MONTH($C10),(DAY($C10)-1)/DAY(EOMONTH(G$9,0))*$F$10,IF(G$9<$C10,0,IF(G$9>$D10,0,$F$10))))

See attached: results in rows 12/13

[oeldere]

I added the formula in the yellow cel.

The result is in the green cells.

Please add the formula on the forum, since I use a dutch version of Excel.

[Surf1987]

@ John Topley Sir! Your really amazing your formula worked like a charm. Thanks a ton for your quick reply .

@ oeldere Thank you so much for your time to reply, your way of approach to the query was really good.
However, except for Allocation start date and Allocation end date remaining months, the revenue should be same irrespective
of no of days be it 30 or 31 days or 28 days which was addressed by John. If it addresses that the formula would be flawless.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Surf1987]

@ JohnTopley Please help with the formula which you have shared in the sheet, as the formula is calculating for the next year as well.
Highlighted the cell in RED for easy find. Thanks

[JohnTopley]

Try

=IF(AND(MONTH(T$9)=MONTH($C10),YEAR($C10)=YEAR(T$9)),((EOMONTH(T$9,0)-$C10+1)/DAY(EOMONTH(T$9,0)))*$F$10,IF(AND(MONTH(T$9)=MONTH($D10),YEAR($D10)=YEAR(T$9)),(DAY($D10)-1)/DAY(EOMONTH(T$9,0))*$F$10,IF(T$9<$C10,0,IF(T$9>$D10,0,$F$10))))

and July figure is 645.