grouping TIME in a pivottable

[liorsitkovsky]

Hi,

First time using this service and first time using excel seriously.
My database is a list of my stock trades. I am a day trader so I have about 1000 rows a day. There are several columns which I am able to play around in my pivot table. However, I can't group my TIME column like I can my date (breaking it down to days, months etc)
Every row (trade) has a specific time associated with it and I am interested in breaking down the TIME column into 3 time intervals (in the pivot table). I am able to do this manually by simply selecting the times and grouping it into new fields in the pivot table but it creates a major problem because every time I add data to the database it does not categorize them into the time interval that I created (as it does with the DATE). When I try to group the time intervals manually (same as I do with DATE by right clicking and grouping) I get an error message advising I can't group this field.

I hope my question is clear enough.

P.S What the maximum amount of data should I put on one page before creating a new page (I am currently at 12000 rows and 10 columns)

Thank you in advance

Lior

 

[stanleydgromjr]

You stated: "Every row (trade) has a specific time associated with it and I am interested in breaking down the TIME column into 3 time intervals (in the pivot table)."

I think that it would be easier to add three columns in your data: Interval 1, Interval 2, Interval 3.

Then add these fields to your Pivot Table.


Have a great day,
Stan

 

[liorsitkovsky]

Thank you Stan for your repy but i don't think that will help me as i am trying to automate the process as much as i can. I want excel to break my time of trades into 3 time intervals and when adding new data to automatically place them in these groups (like it does with DATE, SHARES and all my other fields) I will try what you said but I think it won't work for me because of the way my firm distributes the data to me.

Lior

 

[xl_psychic]

Lior,

I too faced a task as similar to the one mentioned by you. In my case I wanted to group my timings into half an hour intervals to analyse the data. I included a formula generated field called timeint and used the formula =Floor(cell reference, Time(0,30,0)) to group the timings to a half hourly interval. Then use this field in the pivot table.

See if this workaround helps you with your data layout and let me know.

 

[liorsitkovsky]

Stan How do you mean?

I will have 3 seperate time columns? I need excel to recognize time intervals

thank you Lior

 

[stanleydgromjr]

Lior,

What are the three time interval ranges?


Have a great day,
Stan

 

[stanleydgromjr]

Lior,

Actually you would just need one additional column to hold either of the three time intervals.

What are the three time interval ranges?


Have a great day,
Stan