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