0

I need to find the number of records (pid) in the interval (buckets) of 15 minutes for a year long data. Normally when MM/DD/YY H:MM AM, you just pivot the data, put timestamp into row, click on group by and select the interval. However, here I have the timestamp in the format yyyy-MM-dd'T'HH:mm:ssZZZZ and its not letting me group, the floor function is also returning #value, please see the image of reference.

enter image description here

Would appreciate your insights on this issue.

Improve this question
9
  • 1
    The date times are text strings not real date/time. You will need to convert that to a proper date time: =--SUBSTITUTE(SUBSTITUTE(D2,"T"," "),"+",".") should do it. Put that in a blank column and copy/drag it down. Then copy and paste the values over the strings.
    – Scott Craner
    Commented May 22, 2019 at 19:04
  • Thank you for looking into this, with the above I was able to convert say 2013-12-31T08:30:01+0000 to 41639.35418. Now when I pivot the data, put timestamp in rows and select group on right clicking one of the cells I get the error cannot goup the selection. Any idea how I can create bukets of 15 min intervals ?
    – Hemanth Ravavarapu
    Commented May 22, 2019 at 19:21
  • I think I have a solution that uses Pivot Tables (by adding multiple working columns and some math); however, do you require it being in a Pivot Table? Can't you just sort the data by time and write a formula (which I can help with as well) in a new column that create a unique output that identifies which 15-minute group the data belongs to?
    – Brian
    Commented May 22, 2019 at 21:22
  • Hi Brian, no pivot is not necessary anything would do. I'm basically required to group the above createdtime column so that I can create 96 buckets of 15 minutes each (i.e 24 hours) for all the days of the year and then create a chart that shows in which bucket I received the most likes
    – Hemanth Ravavarapu
    Commented May 22, 2019 at 21:27
  • the .35418 part is the fractional part of the day (e.g., .5 would be 12 noon). From there you can convert that to the 15 minute bucket that time belongs to. Then use a bar chart. Let me know if you need more hints on the conversion.
    – gns100
    Commented May 22, 2019 at 23:22

1 Answer 1

Reset to default
0

Here are the following fields you will need to populate:

enter image description here

Next you need to format columns as depicted in the next three images

enter image description here enter image description here enter image description here

Here is what the data will look like when populated with the formulas that are below the image.

enter image description here

After entering each of the formulas in row 2, copy and paste to row 3 to the end of your data.

(paragraph added 2019-05-27 21:34) Note that column F "15 Minute Group ID" is the field that creates the 15-minute groupings. The last two digits of this field are "00","15","30", or "45". They represent the following minute groupings respectively, 0-14, 15-29, 30-44, or 45-59 minutes.

A2: =TEXT(YEAR(G2),"0000")
B2: =TEXT(MONTH(G2),"00")
C2: =TEXT(DAY(G2),"00")
D2: =TEXT(HOUR(G2),"00")
E2: =TEXT(MINUTE(G2),"00")
F2: =VALUE(A2&B2&C2&D2&TEXT(IF(VALUE(E2)<15,0,IF(VALUE(E2)<30,15,IF(VALUE(E2)<45,30,45))),"00"))
G2: =DATE(MID(H2,1,4),MID(H2,6,2),MID(H2,9,2))+TIME(MID(H2,12,2),MID(H2,15,2),MID(H2,18,2))

Now you need to create a Pivot Table.

Within the Pivot Table, add the following fields and then "Group" the "15 Minute Group ID" as shown in the next two images.

enter image description here enter image description here

The Pivot Table will appear as follows after grouping.

enter image description here

Lastly, you can add fields to the table as you want to better illustrate you objectives.

enter image description here

If this solves your problem, please mark this as an answer.

Improve this answer
0

You must log in to answer this question.

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