Google-sheets – Graphs with Google Spreadsheets – collapse data into one ‘category’

The new behavior is likely due to an internal change since the last update introducing the new Google Sheets. My suggestion would be to add an intermediary sheet to connect to the commute chart. The new sheet would contain linked values from the Times sheet. You could then perform arithmetic and formatting operations on it while keeping the Times sheet untouched. At the end, you could also hide the supporting sheet.

An example: https://docs.google.com/spreadsheet/ccc?key=0AvNKw_kDnGPQdHVncl9BNkY0cGIteHpjNVZla0RVMVE&usp=sharing#gid=4

The downside is that the time representation you used to achieve on the chart may no longer be possible. In fact, once formatted the duration values become single points in time instead of differences between times.

To find the approximate usage, say, in June 2015, I would look at the last reading before this month began and at the first reading after it ended; then pro-rate the use accordingly. This guarantees the usage estimate will be based on an interval that's at least a month long.

I used the Match command to determine the relevant rows for each month, then Indirect to access the data. (Another possibility is Vlookup, but it's a bit too rigid for the present problem.) In the sample below:

• column C contains the first day of each month (which can be generated with Date), with custom formatting that shows only the month and year.
• column D has formula =MATCH(C2-1,A:A), etc. This is the last entry in A column preceding the month in column C.
• column E is =TO_TEXT(C2), etc: this conversion to text is needed so that the chart comes out with month-based labels, and not with some other dates chosen by software.
• column F contains the logic of computation: for example, F2 is

=(INDIRECT("B"&D3+1)-INDIRECT("B"&D2))/(INDIRECT("A"&D3+1)-INDIRECT("A"&D2))

meaning that one should take the first reading after the month ended, and subtract the last reading from before it began; then divide by the corresponding dates.

+-----------+--------+-----------+-------+------------+-------+
|   Date    | Meter  |   Month   | Match | Text Month | Usage |
+-----------+--------+-----------+-------+------------+-------+
| 4/25/2015 | 830    | May 15    |     2 | May 15     | 0.33  |
| 5/25/2015 | 844.79 | June 15   |     3 | June 15    | 0.20  |
| 7/1/2015  | 852.2  | July 15   |     3 | July 15    | 0.50  |
| 7/21/2015 | 856.26 | August 15 |     5 | August 15  |       |
| 8/3/2015  | 880    |           |       |            |       |
+-----------+--------+-----------+-------+------------+-------+

The above is also available as a shared spreadsheet.