3

I've got just over 300,000 rows in a spreadsheet, and each of these rows contain a timestamp (HH:MM:SS AM) and a date (DD/MM/YYYY).

I'd like to get counts for these timestamps and group them in 5 minute periods, by day of the week (that is, I'd like the number of timestamps between 12:00AM and 12:05AM on Sunday, 12:05 and 12:10AM on Sunday, etc). I feel that I have to use COUNTIF in some way, but I can't exactly figure out how to implement it. I'm trying to organize the results into another sheet, that looks something like this.

enter image description here

The count for each 5 minute-period would go in that middle column, under the day of the week (so that's where I'd be entering the formula).

Thanks in advance for any help, really appreciate it!

EDIT: I probably should have included that my data looks like this:

enter image description here

Improve this question
1
  • This likely won't be a formula type solution. While possible, it would be faster and easier to write a macro in VBA.
    – Aboba
    Jul 21, 2014 at 14:47

4 Answers 4

Reset to default
1

You can add a couple of fields to your data and then generate a pivot table to get the counts.

First, add a Day of the Week (DOW) field. To get DOW use the formula below, where column B contains the date.

=CHOOSE(WEEKDAY(B2,1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Next, add a field to represent the 5-minute period the timestamp falls in, Time Range. Use the formula below to calculate this, where the timestamp is in column A.

=TIME(HOUR(A2),ROUNDDOWN(MINUTE(A2)/5,0)*5,0)

Once you've added these columns to your data, create a pivot table that uses all your data as its data source.

Set up your pivot table with DOW as column labels and Time Range as row labels. Set the values to Count of Time Range.

enter image description here

Note: This sample looks a little funky because I used sparse data (20 records over a week) to generate the pivot table. It should look more like what you have in mind with your large data set.

Improve this answer
8
  • I appreciate your excellent help! My problem, though, I suppose, is that the records are, for whatever reason, 4 rows apart. So there are actually 4 blank rows between each record, making autofilling the CHOOSE function pretty difficult for all the data. Any ideas?
    – Max
    Jul 21, 2014 at 17:09
  • @Max Well, you're going to need to clean that up before you do anything. I suggest writing a short little VBA macro to delete the extra rows.
    – Excellll
    Jul 21, 2014 at 17:39
  • Hm, ok. didn't realize this before but the rows are actually merged together? The 4 rows that comprise each record, essentially. and the last 3 of every 4 are just blank. This is a headache ;_;
    – Max
    Jul 21, 2014 at 17:46
  • @Max Since this sounds like a bigger problem than what you originally asked about, you should probably post this as a new question.
    – Excellll
    Jul 21, 2014 at 18:05
  • Appreciate your help, @Excellll ! I got a macro to delete the extraneous rows, and I think that, once it's finished running enough times (takes a long time through this enormous file!) I'll be able to implement your solution!
    – Max
    Jul 21, 2014 at 18:44
2

Similar to what @Excellll recommended, you could add an additional column specifying the day of the week, i.e in sheet 1, where your raw data is present and columns A and B hold date and time respectively, you could use this function in column C

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",WEEKDAY(A1))

The approach that I'm using is purely based on functions. Like the image you shared, I customized formulae for Sheet 2 to generate a similar structure. The following condition checks that timestamps exist in a particular range as declared in the first column of the table and additionally that it is a given day of the week. Then it counts all such values.

=COUNTIFS(Sheet1!$B:$B, ">"&$A3,Sheet1!$B:$B,"<="&$A4,Sheet1!$C:$C,"="&B$2)

The image shows how the above formula is entered in cell B1 and has conditions to check that time is in range 12:00 AM to 12:10 AM and day is set to Sunday or 1.

enter image description here

As you might've noticed the time difference here is 10 minutes, I've made it customizable, so you can set it to 5 minutes or 2 hours based on your requirement.

Since we are using functions here, it doesn't matter here if your data has 4 empty rows or 10 empty rows in between. The functions make sure only non blank data are collected. I didn't have enough data so the example looks pretty empty. Please find the sample xls attached here.

Improve this answer
1

Note- going to troubleshoot this code this evening, still having some errors moving down the list of values. Not currently workable, just don't want to reformat my code lines. Feel free to play around. I will add comments and instructions at the same time.

Someone mentioned that this could be done with VBA. I like VBA, so I took a whack at it.

A couple of assumptions:

1 - The time stamp and date are not contained in the same cell (ie 12:00:00 AM | 7/21/2014 NOT 7/21/2014 00:00:00)

2 - You want all of the counts of time stamps grouped within a single listing of days (ie only show one set of Sunday through Saturday, and not create a new set of columns for each additional day - if we started on a Friday, we wouldn't start the grouping on Friday, and if we end on a Tuesday two weeks later, we wouldn't have 16 columns of groupings)

3 - There are no null cells in your data from the first row to the last.

4 - Your timestamps and datestamps data has headers

You should press alt+f11, and open the workbook where you have the data and the source worksheets, and enter this code. Then press F5. 

Public Sub PrintDateGroups()
Dim icontrol As Integer
Dim iweeknum As Integer
Dim ipasscount As Integer
Dim lngwalktimevalues As Long
Dim ipasstimecount As Integer
icontrol = 1

Do Until icontrol = -1:
If ThisWorkbook.Sheets("Data").Cells(icontrol,2).Value = "" Then
    icontrol = -1

Else

    icontrol = icontrol + 1
    iweeknum = Weekday(ThisWorkbook.Sheets("Data").Cells(icontrol, 1).Value, vbSunday)

    For lngwalktimevalues = 0 To 99999999 Step 694444.4375
        If (TimeValue(Format(ThisWorkbook.Sheets("Data").Cells(icontrol, 2).Value, "hh:mm:ss")) * 100000000) <= lngwalktimevalues Then
            If iweeknum = 1 Then
                If ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value <> "" Then
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, 2).Value + 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells(Round((lngwalktimevalues / 694444.4375) + 1, 0), 2).Value = 1
                    Exit For
                End If
            Else
                If ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = "" Then
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = 1
                    Exit For
                Else
                    ThisWorkbook.Sheets("Destination").Cells((lngwalktimevalues / 694444.4375) + 1, ((3 * iweeknum) + (iweeknum - 2))).Value = ThisWorkbook.Sheets("Destination").Cells(lngwalktimevalues / 694444.4375, ((3 * iweeknum) + (iweeknum - 2))).Value + 1
                    Exit For
                End If
            End If
        End If
    Next lngwalktimevalues
End If
Loop
End Sub
Improve this answer
0

You should be able round each record to the nearest five minutes. Then use excel's subtotal function to get the counts.

I'd do the rounding by creating a new column of the date (=Day(A1)) and a second column of the timestamp.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .