Pivot Table Date/Time Grouping Issue

[SkipVought]

Excel 2003

Sample ROW data:
[tt]
5/6/2008 5:28:23
5/6/2008 5:28:22
5/6/2008 3:25:49
5/6/2008 3:25:48
5/6/2008 3:25:47
5/6/2008 3:25:46
5/6/2008 3:25:45
5/5/2008 23:24:55
5/5/2008 23:24:54
[/tt]
Grouping result on Days and Hours
[tt]
5-May 1 AM
1 PM
11 AM
11 PM
3 AM
3 PM
5 AM
5 PM
7 AM
7 PM
9 AM
9 PM
6-May 3 AM
5 AM
[/tt]
The cell format is m/d/yyyy h:mm:ss, but I cannot find a way to change the Days to dd format or the Hours to 24 hour format.

Your mission, should you decide to take it...
is to help Skip find a solution to this conundrum!

As always, all you get for your trouble, is the coveted, pink pointy thing.

Good luck!


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

My work-around was to add 2 columns (L & M) to my source data: (Headings: DD & HH), with formula...
[tt]
=TEXT($I2,L$1)
[/tt]
copy across and down.

I added these two to the ROW area, and grouped the date ROW column by Month.
[tt]
DTE DD HH
May 05 01
03
05
07
09
11
13
15
17
19
21
23
06 03
05
07
[/tt]
But I would still like to get a solution to the group formatting issue.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

Hmmmmm?

This seems klunky, but, I manually changed...
[tt]
1 AM to 01
1 PM to 13
etc.
[/tt]
This change "sticks!" New data for 9:40 AM this morning, automatically formatted as 09, since I had previously change 9 AM to 09.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[SkipVought]

Any other thoughts, before this thread fades away like old soldiers?

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[GlennUK]

Skip, when I group by hour and day it comes out as:
Grouping result on Days and Hours

5-May 23
6-May 03
05

so, am wondering if there is some settings on your machine that are AM/PM for times?

Can't get the date to come out as dd though.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Thanx Glenn. Good to know.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}