Timeline for How to group timestamp in the format yyyy-MM-dd'T'HH:mm:ssZZZZ into buckets of 15 minutes in excel?
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 29, 2019 at 20:05 | vote | accept | Hemanth Ravavarapu | ||
| May 27, 2019 at 6:22 | answer | added | Brian | timeline score: 0 | |
| May 24, 2019 at 18:31 | comment | added | Hemanth Ravavarapu | @Brian: 1) Yes,it is 2)Yes, please. It would really help. | |
| May 24, 2019 at 1:55 | comment | added | Brian | @Hemanth Ravavarapu, I have an solution in Excel for you. First, I want to confirm two things: 1) Is the date/time stamp just a string value; 2) Do you still need this solution (it will take some time to document and post and want to make sure you haven't already found a solution before taking the time to create a post)? | |
| May 23, 2019 at 3:00 | comment | added | Hemanth Ravavarapu | Basically you r trying to draw a yearly chart to see which bucket performed better in a year. So the entire createdtime column needs to be grouped | |
| May 23, 2019 at 0:05 | comment | added | p._phidot_ | for 29.12.2013.. there Is only 7 data points/day.. with a bucket of 15minutes, we will have a table of 96 data points / day [ from calc from (24*60)/15=96 0 ].. what will be the value for the remaining 89 data points? | |
| May 22, 2019 at 23:22 | comment | added | gns100 |
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.
|
|
| May 22, 2019 at 21:27 | comment | added | Hemanth Ravavarapu | 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 | |
| May 22, 2019 at 21:22 | comment | added | Brian | 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? | |
| May 22, 2019 at 19:21 | comment | added | Hemanth Ravavarapu | 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 ? | |
| May 22, 2019 at 19:04 | comment | added | Scott Craner |
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.
|
|
| May 22, 2019 at 19:02 | history | edited | Scott Craner | CC BY-SA 4.0 |
added 10 characters in body
|
| May 22, 2019 at 19:00 | review | First posts | |||
| May 22, 2019 at 19:38 | |||||
| May 22, 2019 at 18:58 | history | asked | Hemanth Ravavarapu | CC BY-SA 4.0 |