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

	A	B	C	D	E	F
1	9/1/2017	9/15/2017	50
2	9/1/2017		100
3	9/1/2017	9/30/2017	100
4		9/18/2017	40
5	9/1/2017		100
6

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 96.875px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

9/1/2017

<tbody>
</tbody>

</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