can't breakup Total Amount in given Date Range to respective Months

Hi all,

I was wondering if what I want to do is possible in Excel. Please refer to this Sample Workbook!
Sample Book.xlsx

Background:

Within a report I receive it gives me a Billing period between 2 dates, sometimes these dates are at the end of one month and the beginning of another month. I must prepare a costing per month view. I have used logic in Excel to break up the “10/19/2014 - 11/1/2014 (Final Invoice)” string into a start and end date. With these dates I am able to find a proportion of the Gross Amount the belongs to each month by prorating the amount with a long complicated formula (to me at least), this is in G:AE. This works fine.

The Problem:

I had to upload a shortened version of the Workbook, but in reality the rows go on to +10,000 and there is a lot more columns. Also, the billing periods start in 2014 and go to 2016. Right now the only way I can prorate the Gross Amount by month, is if the Column Header represents its own Month specific to that year, but when I have 3 years’ worth of date it means I have 36 columns each its own month, soon I will have 4 years of data and so on. I was wondering if I can just have a Generic January – December that isnt for a specific year like 2014, and the formula still worked and knew to prorate the amount and output data in proper month. Like this I would have 12 columns only and I would still know which year it is by just looking the billing period.

Is there a way to do this using IF statements or any statements for that matter? I am open to doing this a new way, but if there is no way I can stick to this long and messy way and keep adding 12 columns as more and more data arrives. Anything helps, even if it’s a simple ‘I don’t think it’s possible'. Thank you kindly in advance you guys are awesome