Results 1 to 6 of 6

Thread: Changing format for grouped hours.

  1. #1
    Seeker Armando L Franco's Avatar
    Join Date
    Aug 2011
    Location
    Ensenada, Baja California, Mexico
    Posts
    5
    Articles
    0

    Changing format for grouped hours.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    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...?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker Armando L Franco's Avatar
    Join Date
    Aug 2011
    Location
    Ensenada, Baja California, Mexico
    Posts
    5
    Articles
    0
    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.
    Click image for larger version. 

Name:	Captura.JPG 
Views:	18 
Size:	43.6 KB 
ID:	205
    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 by Armando L Franco; 2011-08-18 at 01:44 AM.

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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?
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  5. #5
    Seeker Armando L Franco's Avatar
    Join Date
    Aug 2011
    Location
    Ensenada, Baja California, Mexico
    Posts
    5
    Articles
    0
    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. 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 by Armando L Franco; 2011-08-18 at 02:52 PM.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Good catch, Roger. I'm not sure I would have thought of that.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •