Normalizing utility bills into calenfar month???

[PowRider]

Hi smart ppl,

I have been pulling my hair out trying to write a set a vba codes that normalize utility bills into actual calendar month.
So far I have..
Column A as Reading Dates (In order from Jan-Dec) (eg. January 15, 2015)
Column B as Year (In order from smallest to largest) (eg. 2015)
Column C as Days (Billing duration) (eg. 30)
Column D as Consumption (15000kWh)
Column E as Daily Average (Col D/Col C)
Column F as Month Label (Jan, Feb etc..)
Column G as total # of days in the reading date month (eg 31)
Column H as # of days in Col A (eg 15)
Column I as Normalized consumption (calculated using H*E+(G-H)*E of next bill).

The idea end result is a pivot table with column labels as Year and Row labels as Month (12 of them) and data field as consumption.

The logic above works only when the accounts has consistent 12 bills per year (1 per month).
However, a lot of energy accounts have bi monthly bills or 2 reading dates in the same month, then my logic wouldn't work....

Maybe I should've taken a completely different route?
Does anyone have any suggestions? Any help would be appreciated.

Thanks!!

 

[oldbrewer]

	reading dates	31/12/2013	27/01/2014	28/02/2014	01/04/2014	29/04/2014	01/06/2014	30/06/2014	03/08/2014	31/08/2014	02/10/2014	31/10/2014	29/11/2014	30/12/2014
	reading		0	1005	2027	3079	4100	5200	6115	7147	8159	9191	10203	12000	13001
	consumption		1005	1022	1052	1021	1100	915	1032	1012	1032	1012	1797	1001
	days			27	32	32	28	33	29	34	28	32	29	29	31
	relates to			jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
	days in month		31	28	31	30	31	30	31	31	30	31	30	31
	corrected reading		1154	894	1019	1094	1033	947	941	1120	968	1082	1859	1001
			is the last	row the	numbers	you would	expect

<tbody>
</tbody>