Changing format for grouped hours.

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.

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...?

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.

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.

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?

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.

Good catch, Roger. I'm not sure I would have thought of that.