Prorate based on 30 days only and blank cell

[Mandy_84]

Hi! I would be very grateful if you could help me in following. I need a formula prorating certain amount depending on calendar days (only 30 days, but not 28,29 or 31). And Return me blank cell in case if there is no date at all, following formulas don't work
C2=(DATE(YEAR(B2),MONTH(B2)+1,0)-B2+1)/DAY(DATE(YEAR(B2),MONTH(B2)+1,0))*A2
C2=A2*(DAY(EOMONTH(B2,0))-DAY(B2)+1)/DAY(EOMONTH(B2,0))

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Amount[/TD]
[TD]Start date[/TD]
[TD]ProRate Amt[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4.41[/TD]
[TD]09/01/2016
[/TD]
[TD]4.41[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4.41[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4.41[/TD]
[TD]09/06/2016[/TD]
[TD]3.68[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C
1	Amount	Start Date	ProRate Amt
2	4.41	09/01/2016	4.41
3	4.41	-	-
4	4.41	09/06/2016	3.675
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B2="-","-",A2/30*(31-DAY(B2)))

WBD

 

[Mandy_84]

	A	B	C
Amount	Start Date	ProRate Amt
-	-

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4.41[/TD]
[TD="align: right"]09/01/2016[/TD]
[TD="align: right"]4.41[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]4.41[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4.41[/TD]
[TD="align: right"]09/06/2016[/TD]
[TD="align: right"]3.675[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2="-","-",A2/30*(31-DAY(B2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD

Fantastic! thanks a ton! But what if B3 is blank? then C3 shows me 4.56 (((( however I need blank....I tried to give space in " ". but doesnt work....

 

[wideboydixon]

I assumed blank dates were "-" as in your original post. If they're genuinely blank then:

=IF(B2="","",A2/30*(31-DAY(B2)))

If they could be blank or "-" then:

=IF(OR(B2="-",B2=""),"",A2/30*(31-DAY(B2)))

WBD

 

[Mandy_84]

I assumed blank dates were "-" as in your original post. If they're genuinely blank then:

=IF(B2="","",A2/30*(31-DAY(B2)))

If they could be blank or "-" then:

=IF(OR(B2="-",B2=""),"",A2/30*(31-DAY(B2)))

WBD

The second option works ideal!!! Thanks a million times! u saved me!