Hot to get Total by Month, based on interval dates

[romeumc]

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

 

[alansidman]

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.

 

[romeumc]

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

 

[MickG]

Try this for results starting "F1".

[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

 

[romeumc]

Thank you very much, indeed this produces the result i need, but preferred if i could avoid VBA.

 

[MickG]

You're welcome