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

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

<tbody>
</tbody>

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:

Year	Month	Days	Value of kW
2014	4	15	=C2*69/111
2014	5	31	=C3*69/111
2014	6	30	=C4*69/111
2014	7	31	=C5*69/111
2014	8	=5+26	=(5*69/111)+(26*149/51)
2014	9	=26+4	=(26*149/51)+ (4*1361/103)

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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