Help in formulating quarter billing

[fairytinkle]

Hi everyone, I need some help.

If I have different start and end dates and I need to calculate the amount that needs to be allocated every quarter say it starts on 28-Oct-15 (A187)and ends on 27-Feb-17 (B187) and i need to calculate the amount needed to bill in 2017Q4 (G2)
I have used the following and it does not work for those date starting in 2017
*E187 is the monthly cost.

=IF($G$2=MEDIAN($A187,$B187,$G$2),(3-MONTH($A187)+1)*$E187,0)

Please help

[JohnTopley]

Attach a sample workbook (not image)

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[fairytinkle]

Hi John,

thanks for your help.
attached the excel file and manually calculated the highlighted ones as the formula does not give the ideal result that i am looking for.

[JohnTopley]

I don't understand why in ROW 59, F59=20*E59 and then in G59=3*E59.