Rolling Up 15 Minute Interval Data into a 24 Hour Period

[damon_l]

Good Day,

I have time stamp data that occurs in 15 minute intervals with corresponding data in the column next to it and need to add this data up over a 24 hour period.

So in effect I need 30 cells that correspond to 30 days with the total data over that 24 hour period.

Column A is the Time Stamp and Column B is the data.

Below is a sub-section of the data.

What I would like to have is the following:

04/18/2019 150
04/09/2019 200
04/20/2019 250

and so on.

My question is what formula can I use to get these results?


I have a years worth of data in 15 minute intervals that I need to roll up into daily totals?


Cheers
Damon

 

[jasonb75]

Instead of a formula, how about a pivot table?

Pivottable Rows:- Column A
Pivottable Values:- Column B

 

[MoshiM]

Another option

Sub Total_Interval_15_Min()

'////////////////////////////////////////////////////////////////////////////////////////////////
   'Input data will be taken from columns A and B from the active sheet
    'macro will output to columns C and D
'////////////////////////////////////////////////////////////////////////////////////////////////

Dim Data() As Variant, X As Long, Y As Long, Unique_Days() As Variant, UD As Long

With ActiveSheet.UsedRange 'get input from columns A and B on the activesheet
    Data = .Resize(.Rows.count, 2).Value2
End With

ReDim Unique_Days(1 To 2, 1 To UBound(Data, 1))

For X = 1 To UBound(Data, 1)     'Store Unique days within an array

    UD = CLng(CDate(Data(X, 1))) 'string is converted to date and then to long as the match function won't recgonize dates
   
    If IsError(Application.Match(UD, WorksheetFunction.Index(Unique_Days, 1, 0), 0)) Then 'if the day doesn't already exist in the array
        Y = Y + 1                                                                         'then add it
        Unique_Days(1, Y) = UD
    End If
   
Next X

ReDim Preserve Unique_Days(1 To 2, 1 To Y) 'remove columns that weren't used

For X = 1 To UBound(Unique_Days, 2) 'Calculate Totals for each day
   
    For Y = 1 To UBound(Data, 1)
       
        If CLng(CDate(Data(Y, 1))) = Unique_Days(1, X) Then
       
            Unique_Days(2, X) = Unique_Days(2, X) + Data(Y, 2)
      'Else                   'SPEED IMPROVEMENT IF DATA IS SORTED [UNCOMMENT THESE 2 LINES]
            'Exit for
           
        End If
       
    Next Y
   
    Unique_Days(1, X) = CDate(Unique_Days(1, X)) 'convert from long back to date

Next X

Unique_Days = Application.Transpose(Unique_Days)

ActiveSheet.Range("C1").Resize(UBound(Unique_Days, 1), 2).Value2 = Unique_Days

End Sub

 

[damon_l]

Thanks, the VBA code seems to work as I'm getting a new column with larger numbers.
However column A still has the 15 minute interval time stamps, the date's need to be rolled up to daily intervals that correspond to the data.

As an example Column A should have the following time stamp interval now after the data is rolled up to a 24 hour interval.

Column A

04/18/2019
04/19/2019
04/20/2019
.....and so on.

 

[damon_l]

Thanks for that, I tried out the VBA code and it rolls of the date to a 24 hour period and in the corresponding column I am getting a larger number but when cross checking this number when I select all the data over a 24 hour period I get a different result.

An example

For the period 04/18/2019 12:15 AM to 04/18/2019 11:45PM after running the VBA code I get 1 029 but if I select all the data over that same 24 hour period I get 2 241.
Same results with the other cells for other days but obviously different numbers.

I can't attach an excel file to show you the results

Thanks
Regards
Damon

 

[damon_l]

Apologies meant to say smaller number than what it should be.

 

[MoshiM]

It should now overwrite the contents of columns A and B rather changing C and D.
I fixed some other problems I came across as well.
Is your timestamp an actual date or is it text made to look like a date? If it is an actual date then the below code may need some tweaking.

Sub Total_Interval_15_Min()

'////////////////////////////////////////////////////////////////////////////////////////////////
[COLOR=rgb(184, 49, 47)]   'Input data will be taken from columns A and B from the active sheet
   '12:09 AM MST Macro will now overwrite the contents of Columns A and B
   'You will get an error if there isn't a time added to the date[/COLOR]
'////////////////////////////////////////////////////////////////////////////////////////////////

Dim Data() As Variant, X As Long, Y As Long, Unique_Days() As Variant

With ActiveSheet.UsedRange 'get input from columns A and B on the activesheet
    Data = .Resize(.Rows.count, 2).Value
End With

ReDim Unique_Days(1 To 2, 1 To UBound(Data, 1))

For X = 1 To UBound(Data, 1)     'Store Unique days within an array
   
    Data(X, 1) = Split(Data(X, 1), " ")(0)
   
    If IsError(Application.Match(Data(X, 1), WorksheetFunction.Index(Unique_Days, 1, 0), 0)) Then  'if the day doesn't already exist in the array
        Y = Y + 1                                                                         'then add it
        Unique_Days(1, Y) = Data(X, 1)
    End If
 
Next X

ReDim Preserve Unique_Days(1 To 2, 1 To Y) 'remove columns that weren't used

For X = 1 To UBound(Unique_Days, 2) 'Calculate Totals for each day
 
    For Y = 1 To UBound(Data, 1)

        If Data(Y, 1) = Unique_Days(1, X) Then
     
            Unique_Days(2, X) = Unique_Days(2, X) + Data(Y, 2)
[COLOR=rgb(0, 168, 133)]      'Else                   'SPEED IMPROVEMENT IF DATA IS SORTED [UNCOMMENT THESE 2 LINES]
            'Exit for[/COLOR]
         
        End If
     
    Next Y

Next X

Unique_Days = Application.Transpose(Unique_Days)

With ActiveSheet.Range("A:B")
    .ClearContents
    .Resize(UBound(Unique_Days, 1), 2).Value2 = Unique_Days
End With

End Sub

 

[jasonb75]

Did you try the pivot table method? I know that it works because I use it for something similar.

Another way would be with simple formulas and filters. Assuming that you have headers in row 1 (data starting in row 2).

In C2, and fill down
=IF(INT(A2)=INT(A3),"",INT(A2))

In D2 and fill down
=IF(C2="","",SUM(B$2:B2)-SUM(C$1:C1))

Then filter to hide the blanks. Copy and paste if desired.

 

[damon_l]

Thanks I will try this method.