grouping in a pivottable

[liorsitkovsky]

I am sorry I am posting the same question for the second time but i think i wasn't clear enough so i have included a line from my data

SYMBOL PRICE SHARES GATEWAY PER_SHARE DATE TIME
SIRI 4.3000 300 ARCA .0000 08/01/ 09:13

There are more columns but for simplicity I included only this. There are about 12000 rows of data. I am able to group all fields in my pivot table but not the TIME field. I can group it manually but the implication of that is that when I had new data it is not included in my time grouping.
I am interested to group it into 3 time intervals (9:30 to 11:30, 11:30 to 14:00, and 14: to 16:00). When I right click on the TIME filed in the pivot table I get an error saying I can't group this field. On the other hand I am able to group my DATE field into months, days, years etc.
I can group it manually but then when new data is added the time of the trades is excluded from the groups. This is important to me because I am trying to get my trading performance based on time of day. Any suggestions please. I am desperate and tried everything by myself so far.

Lior

 

[xl_psychic]

Lior,

I have answered your previous post on the same subject. Since you want to group your data into three zones of time, I suggest you include a formula based column in your pivot and use the Vlookup function with the True argument to segregate your times into 3 zones.

Your Vlookup table can be setup as

Time Zone
09:30:00 09:30 to 11:30
11:30:01 11:30 to 14:00
14:00:01 14:00 to 16:00

Doing a Vlookup(cell reference, Data Table as given above, 2, True) will segregate your times in the required zone. You can then drag and drop this newly created column of data into your pivot.

Hope this helps..

 

[liorsitkovsky]

Thank you. Will try