# Convert randomly dated entries to monthly and/or daily total

#### archipelag0

##### New Member
I have a series of data entries (recording electricity usage in kWh) that were taken on various random days of the year.
I'd like to summarise this into monthly totals and ideally find a daily average for each month.

As an example:
 Monday, December 28, 2020 5081 Saturday, January 09, 2021 5413 Monday, January 25, 2021 5543 Tuesday, February 09, 2021 5634 Tuesday, March 02, 2021 5847 Tuesday, March 09, 2021 5922

So for January, for example since the first data wasn't taken for a few days it would need to be reckoned backwards using the values from December. Likewise February could be determined from the average number of days in the month and the dates either side. Effectively it's 'chopping' an interpolated curve of points into monthly/daily slices.

I looked at SUMPRODUCT and EOMONTH but can't quite find the right way to do this.

Can anyone help?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Try write your dates, as in example here. Then it will be easier to sum your monthly total.

Mappe3
ABCDE
1DatesvaluesMonthsTotal
228-12-2020508101-12-20205081
309-01-2021541301-01-202110956
425-01-2021554301-02-20215634
509-02-2021563401-03-202111769
602-03-20215847
709-03-20215922
Sheet1
Cell Formulas
RangeFormula
E2:E5E2=SUMIFS(\$B\$2:\$B\$7,\$A\$2:\$A\$7,">="&D2,\$A\$2:\$A\$7,"<="&EOMONTH(D2,0))

@ebea thank you for the prompt response. What I should have said is that the total in the values (column B) is cumulative, so the figure in your example E3 is incorrect as it's a running total not a discrete amount. I want to figure out what the values have increased by on a daily and monthly basis.

@ebea thank you for the prompt response. What I should have said is that the total in the values (column B) is cumulative, so the figure in your example E3 is incorrect as it's a running total not a discrete amount. I want to figure out what the values have increased by on a daily and monthly basis.
In that case, it will be a bit tricky, as some months only have 1 reading, and other 2 or more. So a MAX-MIN solution between dates, would also not work, hence the readings you have. Maybe other have a better solution.

Try this, and see if it do what you want.

Mappe1
ABCDEF
1DatesvaluesMonthsTotal
228-12-2020508101-12-202031-12-2020462
309-01-2021541301-01-202131-01-202191
425-01-2021554301-02-202128-02-2021288
509-02-2021563401-03-202131-03-20210
602-03-2021584701-04-202130-04-20210
709-03-2021592201-05-202131-05-20210
Ark1
Cell Formulas
RangeFormula
F2:F7F2=MAX(IF(\$A\$2:\$A\$7>=D3,IF(\$A\$2:\$A\$7<=E3,\$B\$2:\$B\$7)-MAX(IF(\$A\$2:\$A\$7>=D2,IF(\$A\$2:\$A\$7<=E2,\$B\$2:\$B\$7)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

@ebea, thanks it certainly gives some figures in the total column, but I notice that for several months I get a zero value which clearly can't be the case.

Can you confirm what your formula is doing? I understand the IF statements are looking to see if the date of the record sits within a certain month (between dates in columns D & E), but I don't get how the MAX function is used here, or why some values are returned as zero.

@ebea, thanks it certainly gives some figures in the total column, but I notice that for several months I get a zero value which clearly can't be the case.

Can you confirm what your formula is doing? I understand the IF statements are looking to see if the date of the record sits within a certain month (between dates in columns D & E), but I don't get how the MAX function is used here, or why some values are returned as zero.
When it comes to zero values, it's because there's no data to calculate.

Yes, IF are as you write, to find the start and end of a month period. Max are to take the max value from previous month, and suctract the next month.

So as I do not knows your exact data, I can't write an exact period. So only you have your actual numbers, and can fill in these, for each month. And by doing so, I guess you will get rid of zero values.
This here, is only a proposal, out from your picture.

Replies
4
Views
619
Replies
1
Views
364
Replies
3
Views
295
Replies
3
Views
292
Replies
0
Views
222

1,202,987
Messages
6,052,939
Members
444,617
Latest member
Rush1984

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back