Changing format for grouped hours.

Armando L Franco

New member
Joined
Aug 17, 2011
Messages
5
Reaction score
0
Points
0
Location
Ensenada, Baja California, Mexico
Hi.

When grouping by hour in a pivot table, sometimes the result will be in 24-hour format,

Time:
11
12
13

and sometimes it will have AM/PM on it.

Time
11AM
12PM
01PM

Would you please help me find where this formatting comes from?

Thanks!
Armando.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
I woudl think it would come from the format of the data in your source table. You should be able to control it by right clicking it, choosing Value Field Settings --> Number Format and setting it to the desired number format though... unless I'm missing your question...?
 

Armando L Franco

New member
Joined
Aug 17, 2011
Messages
5
Reaction score
0
Points
0
Location
Ensenada, Baja California, Mexico
Thanks, Ken.

I tried that. No matter what the format is on the source data, my pivot tables show up as 10 a.m. and 01 p.m. If I transfer the file to a friend's computer, they will show up as 10 and 13.¨

I also tried Value Field Settings - Number Format and assigned even [h] as the format, but it will still show 10 a.m.
Captura.JPG
Here I am copying source data and grouped pivot tables. It is not such a big deal, but I am puzzled.

I'll appreciate any tip on where to look for the setting.

Thanks.
Armando.
 
Last edited:

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi Armando

I tried setting up the same thing in XL2007 and XL2010 and I get the straight numeric 10, 11 12 etc in all cases.
I don't know of any setting that would affect this, but I would be interested if you could upload a copy of your file so I can load it on my system and see the result.

What do you use for Regional Settings?
Do your colleagues have the same Regional Settings as you?
 

Armando L Franco

New member
Joined
Aug 17, 2011
Messages
5
Reaction score
0
Points
0
Location
Ensenada, Baja California, Mexico
Thanks, Roger!

That did the trick. The format for the grouped hour in pivot tables comes from the long hour format in Regional Settings. Now I can sleep better at night. :)

Here's the file. View attachment Sample file.xlsx

When I change the format in Regional Settings and then refresh the pivot, the hour changes from 13 and 1 p.m.

Best regards.
Armando.
 
Last edited:
Top