Prorate based on start and end date in a month

[Mandy_84]

Hi People,
I'm looking for function that would prorate me a amount based on start and end date, however in case I don't have end date, then it picks only start date
the full amount is 100, so Column C should show me either full amount or prorated or gives blank if non of dates given
the function
=DAY(B2)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0))*100 doesn't really work ((( it picks only end date and disregards start date

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/1/2017[/TD]
[TD]9/15/2017[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9/1/2017[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9/1/2017[/TD]
[TD]9/30/2017[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]9/18/2017[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9/1/2017[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 96.875px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]9/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[admiral100]

Hi,

Try this

=IF(B1-A1<0,100,IF(B1-A1=0,"",((DAY(B1)-DAY(A1))+1)*100/DAY(EOMONTH(A1,0))))

 

[Mandy_84]

perfect!!! thanks a ton