show only hour in pivot table

[Junk]

Please see the example below. I have a bunch of time values that I have converted to local times. The times are formatted as times and have dates attached to them. What I'm trying to show is a pivot table with the dates in rows and the hours requiring coverage in columns. When I do this, my columns end up being way too many as it still maintains the date with the hour.

I'd like the rows to be like 1/14, 1/15, 1/16 etc, then the columns being 8am, 9am, 10am etc. I'm trying to show the times we need coverage for the scheduled events.

Any help would be greatly appreciated. Thanks.

Book9
	A	B	C	D	E	F	G	H	I
1	FullLocation	Retailer	Type	LocalTime	Str	Location	Day	Date	Time
2	X-Store-01111	X	Store	8:00AM	1111	OR	FRI	1/18/2008	11:00
3	y-Store-02222	y	Store	7:00AM	2222	AL	FRI	1/18/2008	8:00
4	z-DC-03333	z	DC	5:00PM	3333	MN	FRI	1/18/2008	18:00
5	X-DC-44444	X	DC	7:00AM	44444	MS	FRI	1/18/2008	8:00
All Locations

Would like it to look similar to the below.

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M
8		Time
9	Date	7am	8am	9am	10am	11am	12am	1pm	2pm	3pm	4pm	5pm	6pm
10	18-Jan	2	1									1
All Locations

 

[General Ledger]

Junk,

In a Pivot Table, you can group based on hours, days, month, etc. Assume column A of the PT is your field called DATE.
1. Place the cursor on any entry below the column heading.
2. Right click to display a pop up menu.
3. Select Group and Show Detail
4. Select Group... from the small pop up menu
5. Select the time elements you want to group by

Use the scroll bar on the right to move up and down the list of options. You can select more than one option (say Year and Month) to create more than one level of groupings.

I hope this helps.

Good luck,

General Legder

 

[Junk]

Sorry for the late reply, but that's perfect. Never knew that was an option. Thanks.

 

[General Ledger]

Junk,

I am so happy to hear I was able to help. I used pivot tables for years before I learned about grouping on time.

Here is something else I did not know for may years. If you have a pivot table with a Page item, Excel can create a seperate worksheet (each with the same pivot table) for each Page item.

Using your data, suppose you have a pivot table with LOCATION as a page item. When you click on the down-pointing arrow to the right, you can select one LOCATION so only records related to that LOCATION are included in the pivot table.

Right click on the main toolbar area to display the list of all toolbars you can add. Check the one for Pivot Table to diplay the toolbar.

Have your cursor somewhere on the pivot table. From the Pivot Table toolbar, click the Pivot Table option to display a menu of options. Select the last item, Show pages....

A pop-up menu asks for the field to use. Select field and OK.

Excel does the rest.

Enjoy.

GL