grouping TIME in a pivottable

liorsitkovsky

New Member
Joined
Sep 15, 2006
Messages
9
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Joined
Jul 30, 2006
Messages
3,656
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

New Member
Joined
Sep 15, 2006
Messages
9
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

Active Member
Joined
Jan 4, 2004
Messages
359
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

New Member
Joined
Sep 15, 2006
Messages
9

ADVERTISEMENT

Stan How do you mean?

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

thank you Lior
 
Joined
Jul 30, 2006
Messages
3,656
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,999
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top