# Access or Excel question: Convert billing cycles into natural months?
**URL:** https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988
**Category:** Factual Questions
**Created:** [July 8, 2013, 11:38pm UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988 "2013-07-08T23:38:38Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Reply](https://boards.straightdope.com/u/Reply)
#### Post date: [July 8, 2013, 11:38pm UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/1 "2013-07-08T23:38:38Z")
From a utility, let’s say I get data like this:
```auto
kWh Used | Meter Read Date
=========|================
56 | 12/16/2010
78 | 11/14/2010
69 | 10/12/2010
33 | 9/16/2010
```
Where the meter is read on a random day APPROXIMATELY a month after the previous date. I want to analyze that by month instead of billing cycle (i.e., usage for October and November).
I’m thinking of doing it by averaging and adding:
For October, it would be (69 \* 12/31) + ( 78 \* (14/30 + 31-12))
Meaning I’d use the first 12 days from the October billing cycle and then the remaining 18 days of October using data from the November billing cycle.
Does that seem logical, and is there an easier (maybe built-in?) way to do this?
Thank you!
---
### Author:  [@DataX](https://boards.straightdope.com/u/DataX)
#### Post date: [July 8, 2013, 11:48pm UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/2 "2013-07-08T23:48:03Z")
It is somewhat logical.
I know of no built in way to do it. Basically I would do it the way you said - in excel - with a lookup table for each month indicating length of month (yes you could do it with a fancier formula, but I’m lazy).
FWIW - if you are analyzing your own personal data - very rarely have I found adjustments like these to improve the analysis. It is easier to just pretend the readings for September are for all of September.
I’ve made adjustments like this to accommodate data from different timezones and such. Even when presenting the data to smart people - and explaining what I did - no one but me seems to care. It never occurs to other people to think about these things (or very rarely).
---
### Author:  [@Reply](https://boards.straightdope.com/u/Reply)
#### Post date: [July 9, 2013, 1:54am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/3 "2013-07-09T01:54:34Z")
I just noticed a few errors in that formula (missed September’s contribution to the October billing cycle, reversed November & October in the second part).
> [@DataX](#):
>
> FWIW - if you are analyzing your own personal data - very rarely have I found adjustments like these to improve the analysis. It is easier to just pretend the readings for September are for all of September.
I don’t think my boss would be very happy with that 🙂 It’s for a university that uses several MWh a month across a few dozen buildings and the bad approximations add up quickly.
---
### Author:  [@DataX](https://boards.straightdope.com/u/DataX)
#### Post date: [July 9, 2013, 2:06am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/4 "2013-07-09T02:06:17Z")
Well then by all means - get as particular as you can 🙂
I assume you know about the MONTH function? It will give you the month (1-12) from most standard date formats. You can use that as a start - or there maybe another way, but that is how I’d do it. MONTH to get a number from 1 to 12 - and then use VLOOKUP to get max days from a manual 12x2 table. And then use the formula you hinted at. Sort of assumes you have data from every month.
---
### Author:  [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)
#### Post date: [July 9, 2013, 2:36am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/5 "2013-07-09T02:36:36Z")
That actually kind of what utilities have to do when we do 10-Ks and 10-Qs (but backwards). We pro-rata out the amount of unbilled days to estimate how much service we’ve delivered without billing, since it’s technically an asset.
That said, I think you have a slightly easier/better way to do the formulas.
You can actually subtract one date from another in Excel and you will get a number of days as an answer.
So, what I would do for October would be:
([69 / (10/12/2010 - 9/16/2010)] \* day(10/12/2010) + (78 / (11/14/2010 - 10/12/2010) \* ([eom(10/12/2010,0)] - 10/12/2010)
Of course, the dates would be cell references, so you should be able to put the whole thing together without any hard codes or vlookups.
eta: the eom function take any date as an input and produce the last day of that month plus however many months you put after the comma. So, eom(6/5/2013,1) will equal 7/31/2013. A 0 will give you the last date of the month entered.
The day command will take any date as an input and output a number equal to the day of the month. So day(5/7/2013) equals 7.
---
### Author:  [@DataX](https://boards.straightdope.com/u/DataX)
#### Post date: [July 9, 2013, 2:44am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/6 "2013-07-09T02:44:06Z")
> [@Darth\_Panda](#):
>
> That actually kind of what utilities have to do when we do 10-Ks and 10-Qs (but backwards). We pro-rata out the amount of unbilled days to estimate how much service we’ve delivered without billing, since it’s technically an asset.
>
> That said, I think you have a slightly easier/better way to do the formulas.
>
> You can actually subtract one date from another in Excel and you will get a number of days as an answer.
>
> So, what I would do for October would be:
>
> ([69 / (10/12/2010 - 9/16/2010)] \* day(10/12/2010) + (78 / (11/14/2010 - 10/12/2010) \* ([eom(10/12/2010,0)] - 10/12/2010)
>
> Of course, the dates would be cell references, so you should be able to put the whole thing together without any hard codes or vlookups.
>
> eta: the eom function take any date as an input and produce the last day of that month plus however many months you put after the comma. So, eom(6/5/2013,1) will equal 7/31/2013. A 0 will give you the last date of the month entered.
>
> The day command will take any date as an input and output a number equal to the day of the month. So day(5/7/2013) equals 7.
This is better than my idea. Didn’t know about eom function - might come in handy for me - if only I remember it’s there…
---
### Author:  [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)
#### Post date: [July 9, 2013, 3:00am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/7 "2013-07-09T03:00:28Z")
I did it using a crude combination of a VLOOKUP and a pivot table. Here is the spreadsheet: [http://slamfrog.com/kw.xls](http://slamfrog.com/kw.xls)
Here is the answer for the sample data. It is also in the pivot table highlighted in yellow in the spreadsheet.
```auto
Month Total
9 72.808
10 76.465
11 60.477
12 26.250
```
Hopefully, it is correct. 🙂
---
### Author:  [@Reply](https://boards.straightdope.com/u/Reply)
#### Post date: [August 9, 2013, 1:20am UTC](https://boards.straightdope.com/t/access-or-excel-question-convert-billing-cycles-into-natural-months/662988/8 "2013-08-09T01:20:42Z")
Thank you all for the help. It took me a few weeks to make and debug, but ultimately I did average out usage across neighboring billing cycles. The eomonth() function helped tremendously.
Here’s the finished product if you anyone wants to take a look:
[http://asdfv.com/bills.xlsx](http://asdfv.com/bills.xlsx)
Thank you all again!