Hot to get Total by Month, based on interval dates

romeumc

New Member
Joined
Feb 19, 2019
Messages
4
I have the following data of electricity consumption on Office 365:

Note: date format is dd/mm/yyyy

[TABLE="width: 500"]
<tbody>[TR]
[TD]Begin[/TD]
[TD]End[/TD]
[TD]Value of kW[/TD]
[/TR]
[TR]
[TD][TABLE="width: 65"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]16/04/2014[/TD]
[/TR]
[TR]
[TD="align: right"]06/08/2014[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2014[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]22/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]23/04/2015[/TD]
[/TR]
[TR]
[TD="align: right"]15/05/2015[/TD]
[/TR]
[TR]
[TD="align: right"]03/07/2015[/TD]
[/TR]
[TR]
[TD="align: right"]05/08/2015[/TD]
[/TR]
[TR]
[TD="align: right"]04/09/2015[/TD]
[/TR]
[TR]
[TD="align: right"]13/10/2015[/TD]
[/TR]
[TR]
[TD="align: right"]20/11/2015[/TD]
[/TR]
[TR]
[TD="align: right"]23/12/2015[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]22/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]24/02/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14/04/2016[/TD]
[/TR]
[TR]
[TD="align: right"]21/05/2016[/TD]
[/TR]
[TR]
[TD="align: right"]16/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]08/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]07/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]20/10/2016[/TD]
[/TR]
[TR]
[TD="align: right"]19/11/2016[/TD]
[/TR]
[TR]
[TD="align: right"]24/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]06/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]13/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]24/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]10/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]25/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]07/04/2017[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2017[/TD]
[/TR]
[TR]
[TD="align: right"]17/06/2017[/TD]
[/TR]
[TR]
[TD="align: right"]11/07/2017[/TD]
[/TR]
[TR]
[TD="align: right"]21/08/2017[/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2017[/TD]
[/TR]
[TR]
[TD="align: right"]14/10/2017[/TD]
[/TR]
[TR]
[TD="align: right"]24/11/2017[/TD]
[/TR]
[TR]
[TD="align: right"]22/12/2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]25/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]22/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]23/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]18/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]18/05/2018[/TD]
[/TR]
[TR]
[TD="align: right"]28/05/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17/08/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]22/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]22/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]05/08/2014[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2014[/TD]
[/TR]
[TR]
[TD="align: right"]08/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]21/01/2015[/TD]
[/TR]
[TR]
[TD="align: right"]22/04/2015[/TD]
[/TR]
[TR]
[TD="align: right"]14/05/2015[/TD]
[/TR]
[TR]
[TD="align: right"]02/07/2015[/TD]
[/TR]
[TR]
[TD="align: right"]04/08/2015[/TD]
[/TR]
[TR]
[TD="align: right"]03/09/2015[/TD]
[/TR]
[TR]
[TD="align: right"]12/10/2015[/TD]
[/TR]
[TR]
[TD="align: right"]19/11/2015[/TD]
[/TR]
[TR]
[TD="align: right"]22/12/2015[/TD]
[/TR]
[TR]
[TD="align: right"]08/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]21/01/2016[/TD]
[/TR]
[TR]
[TD="align: right"]23/02/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13/04/2016[/TD]
[/TR]
[TR]
[TD="align: right"]20/05/2016[/TD]
[/TR]
[TR]
[TD="align: right"]15/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]29/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]07/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]06/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]19/10/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18/11/2016[/TD]
[/TR]
[TR]
[TD="align: right"]23/12/2016[/TD]
[/TR]
[TR]
[TD="align: right"]05/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]19/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2017[/TD]
[/TR]
[TR]
[TD="align: right"]12/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]23/02/2017[/TD]
[/TR]
[TR]
[TD="align: right"]09/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]24/03/2017[/TD]
[/TR]
[TR]
[TD="align: right"]06/04/2017[/TD]
[/TR]
[TR]
[TD="align: right"]17/05/2017[/TD]
[/TR]
[TR]
[TD="align: right"]16/06/2017[/TD]
[/TR]
[TR]
[TD="align: right"]10/07/2017[/TD]
[/TR]
[TR]
[TD="align: right"]20/08/2017[/TD]
[/TR]
[TR]
[TD="align: right"]02/09/2017[/TD]
[/TR]
[TR]
[TD="align: right"]13/10/2017[/TD]
[/TR]
[TR]
[TD="align: right"]23/11/2017[/TD]
[/TR]
[TR]
[TD="align: right"]21/12/2017[/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2017[/TD]
[/TR]
[TR]
[TD="align: right"]17/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2018[/TD]
[/TR]
[TR]
[TD="align: right"]21/02/2018[/TD]
[/TR]
[TR]
[TD="align: right"]09/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]22/03/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]17/05/2018[/TD]
[/TR]
[TR]
[TD="align: right"]27/05/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16/08/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16/09/2018[/TD]
[/TR]
[TR]
[TD="align: right"]16/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]21/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]21/12/2018[/TD]
[/TR]
[TR]
[TD="align: right"]31/12/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]69[/TD]
[/TR]
[TR]
[TD="align: right"]149[/TD]
[/TR]
[TR]
[TD="align: right"]1361[/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[/TR]
[TR]
[TD="align: right"]4871[/TD]
[/TR]
[TR]
[TD="align: right"]380[/TD]
[/TR]
[TR]
[TD="align: right"]685[/TD]
[/TR]
[TR]
[TD="align: right"]257[/TD]
[/TR]
[TR]
[TD="align: right"]266[/TD]
[/TR]
[TR]
[TD="align: right"]803[/TD]
[/TR]
[TR]
[TD="align: right"]648[/TD]
[/TR]
[TR]
[TD="align: right"]792[/TD]
[/TR]
[TR]
[TD="align: right"]609[/TD]
[/TR]
[TR]
[TD="align: right"]215[/TD]
[/TR]
[TR]
[TD="align: right"]253[/TD]
[/TR]
[TR]
[TD="align: right"]1081[/TD]
[/TR]
[TR]
[TD="align: right"]1797[/TD]
[/TR]
[TR]
[TD="align: right"]1238[/TD]
[/TR]
[TR]
[TD="align: right"]506[/TD]
[/TR]
[TR]
[TD="align: right"]194[/TD]
[/TR]
[TR]
[TD="align: right"]382[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD="align: right"]670[/TD]
[/TR]
[TR]
[TD="align: right"]631[/TD]
[/TR]
[TR]
[TD="align: right"]1078[/TD]
[/TR]
[TR]
[TD="align: right"]228[/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]261[/TD]
[/TR]
[TR]
[TD="align: right"]1153[/TD]
[/TR]
[TR]
[TD="align: right"]230[/TD]
[/TR]
[TR]
[TD="align: right"]-230[/TD]
[/TR]
[TR]
[TD="align: right"]317[/TD]
[/TR]
[TR]
[TD="align: right"]341[/TD]
[/TR]
[TR]
[TD="align: right"]335[/TD]
[/TR]
[TR]
[TD="align: right"]595[/TD]
[/TR]
[TR]
[TD="align: right"]460[/TD]
[/TR]
[TR]
[TD="align: right"]291[/TD]
[/TR]
[TR]
[TD="align: right"]492[/TD]
[/TR]
[TR]
[TD="align: right"]179[/TD]
[/TR]
[TR]
[TD="align: right"]758[/TD]
[/TR]
[TR]
[TD="align: right"]452[/TD]
[/TR]
[TR]
[TD="align: right"]684[/TD]
[/TR]
[TR]
[TD="align: right"]226[/TD]
[/TR]
[TR]
[TD="align: right"]386[/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[/TR]
[TR]
[TD="align: right"]1136[/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]549[/TD]
[/TR]
[TR]
[TD="align: right"]594[/TD]
[/TR]
[TR]
[TD="align: right"]487[/TD]
[/TR]
[TR]
[TD="align: right"]145[/TD]
[/TR]
[TR]
[TD="align: right"]1112[/TD]
[/TR]
[TR]
[TD="align: right"]324[/TD]
[/TR]
[TR]
[TD="align: right"]378[/TD]
[/TR]
[TR]
[TD="align: right"]610[/TD]
[/TR]
[TR]
[TD="align: right"]742[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


What i want is to create a table by YEAR/MONTH, with the proportional sum of each month.
Thanks for any help.
Romeu
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
For the first three or four items in your list, what would the result look like. Mock it up manually, so that we completely understand your needs.
 
Upvote 0
Hi, thanks in advance.
What i would like to get is a resume like this:

[TABLE="width: 570"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Days[/TD]
[TD]Value of kW[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]=C2*69/111[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]5[/TD]
[TD]31[/TD]
[TD]=C3*69/111[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]6[/TD]
[TD]30[/TD]
[TD]=C4*69/111[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]7[/TD]
[TD]31[/TD]
[TD]=C5*69/111[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]8[/TD]
[TD]=5+26[/TD]
[TD]=(5*69/111)+(26*149/51)[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]9[/TD]
[TD]=26+4[/TD]
[TD]=(26*149/51)+
(4*1361/103)[/TD]
[/TR]
</tbody>[/TABLE]


Notes: 111, 51, 103 are the number of days between the first 3 intervals.
69, 149, 1361 are the values of the first 3 rows on the data table
 
Upvote 0
Try this for results starting "F1".
Code:
[COLOR="Navy"]Sub[/COLOR] MG20Feb34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] DT [COLOR="Navy"]As[/COLOR] Date, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count * 365, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")

.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Num = DateDiff("d", Dn.Value, Dn.Offset(, 1))
    [COLOR="Navy"]For[/COLOR] DT = Dn.Value To Dn.Offset(, 1).Value
        [COLOR="Navy"]If[/COLOR] Not .exists(Year(DT) & "," & Month(DT)) [COLOR="Navy"]Then[/COLOR]
            .Add Year(DT) & "," & Month(DT), 1
        [COLOR="Navy"]Else[/COLOR]
            .Item(Year(DT) & "," & Month(DT)) = .Item(Year(DT) & "," & Month(DT)) + 1
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] DT
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        c = c + 1
        ray(c, 1) = K: ray(c, 2) = (.Item(K) / Num) * Dn.Offset(, 2).Value
    [COLOR="Navy"]Next[/COLOR] K
.RemoveAll
[COLOR="Navy"]Next[/COLOR] Dn

'[COLOR="Green"][B]Range("I1").Resize(c, 2) = ray[/B][/COLOR]
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray)
    [COLOR="Navy"]If[/COLOR] Not .exists(ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
        .Add (ray(n, 1)), ray(n, 2)
    [COLOR="Navy"]Else[/COLOR]
        .Item(ray(n, 1)) = .Item(ray(n, 1)) + ray(n, 2)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Range("F1").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top