How to get sum value from start time and end time?-CodePudding

I have a start time, duration and a data value in Columns A, B and C respectively.

How can I capture the data that falls during the start time and end time and insert the sum of this data in a 30-minute cycle (e.g. 09:00, 09:30, 10:00, 10:30 etc) in the "Output" column?

For example, if Data_A had a start time was at 09:15 and end time at 10:15, its value would be returned at 09:00, 09:30 and 10:00.

If more than 1 data value was received within the same 30-minute cycle, the values would be summed. For example, Data_A has a value of 0.1 and Data_B has a value of 0.2. Data_B has a start time at 09:50 and end time at 10:10. The sum values at 09:00, 09:30 and 10:00 would be 0.1, 0.3 and 0.3 respectively.

If no data was received for any 30-minute cycle, it simply returns a zero.

The following SUMIFS function can be used to sum the values at start time but I couldn't modify it to take into account the duration and end time.

=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&G2,$A$2:$A$10,"<"&G2 TIME(0,29,59))

The dataset I have is over a year's worth, I am open to solutions using cell equations or VBA.

CodePudding user response：

I would create a VBA function SumBetweenDateTimeLoop with inputs the cell of the starting datetime, the range of starting time, range of ending time, range of values to add and minutes of the loop (in this case fixed to 30).

Public Function SumBetweenDateTimeLoop(this_date As Range, dt_start As Range, dt_end As Range, values As Range, min_loop As Integer)

    Dim i As Integer
    Dim v As Double
    Dim d1 As Date, d2 As Date
    Dim v1 As Date, v2 As Date
    
    If dt_start.Count <> dt_end.Count Or values.Count <> dt_end.Count Or dt_start.Count <> values.Count Then
        Call MsgBox("Length of ranges have to be the same.")
        Exit Function
    End If

    v = 0
    For i = 1 To dt_start.Count
        d1 = CDate(dt_start(i))
        d2 = CDate(dt_end(i))
        v1 = CDate(this_date)
        v2 = DateAdd("n", min_loop, v1)

        If Not ((d1 < v1 And d2 < v1) Or (d1 > v2 And d2 > v2)) Then
            v = v   CDbl(values(i))
        End If
    Next i
    
    SumBetweenDateTimes = v

End Function

The result is reported in the red column (there are some differences from your green column but it should be correct if I understood correctly tour problem).

In order to call the function (after have copied it into a module in VBA), type in the desired Excel cell the following request.

=SumBetweenDateTimeLoop(G2;A$2:A$10;E$2:E$10;C$2:C$10;30)

Code explanation

The idea is to define extremes of the cycle, so define v1 as the datetime in column G and v2 = v1 min_loop.

Then make a loop over the cells of the ranges (Excel columns A, E and C), define the extremes of datetime (d1 start datetime in column A and d2 ending datetime in column E) and check whether or not both d1 and d2 are lower than v1 or greather than v2. If this condition is not satisfied, it means that at least a part of the time is inside the desired cycle and then add the relative value to the incremental parameter v, which at the end will be the return of the function.

CodePudding user response：

It's not that difficult: the solution is 48 :-)

By this I mean that, in Excel, and entire day is one unit (e.g. 01/01/2022 1 = 02/01/2022).
As a result, one hour equals 1/24th and half an hour (30 minutes) corresponds with 1/48th of a day.

So, you want to round down to 1/48, which you can do using this simple formula:

=FLOOR(B2*48,1)/48

Hereby some examples: