1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Pivot Table Date is in wrong Format and I can't get it to change

Discussion in 'Business Applications' started by SlowHnds, Mar 31, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    in my data source the date field is custom ddd mmm dd yyyy

    When I create the pivot table it shows as dd-mmm but when I try to format the field it already shows the format correctly but the sample show dd mmm

    ie. Source table shows Thu Mar 26 2015
    Pivot Table Shows 26-Mar

    I am unable to reformat it to show the source.

    So how do I get the pivot table to display the right date format?
     

    Attached Files:

  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Have you definitely ensured all cells in that column are formatted the same in the source table?


    I formatted the current date as you describe and created a pivot table with that data and Excel automatically used the same formatting. I'm using Excel 2013.
     
  3. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    All cells are formatted to display the same.

    I think I've found part of the issue in that I wanted to put date and time in

    ie. ddd mmm dd yyyy h:m AM/PM and entered data for the change but it wouldn't total correctly without grouping and the grouping is what changes the date to dd-mmm and it can then not be formatted any differently. As soon as I took out the h:mm entries it shows up displayed correctly in pivot table but I want the date and time on the data table. So I'm sort of at a one or the other with neither being perfect.

    So I do without the times and settle for the short date or do without the times but get Thu March 26, 2015 in the pivot table. The times do make the data more valuable as it can show trends.
     
  4. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Because I have multi-months and I want grouped by Month 1 day

    The pivot table shows dd MMM ie.

    26 Mar
    27 Mar
    28 Mar
    29 Mar
    30 Mar
    31 Mar
    1 Apr
    2 Apr
    3 Apr

    Nothing I do in the pivot table changes the date format

    It has something to do with the grouping because if I ungroup the date format shows up correctly as it is formated in the data table. ie
    Tue Mar 31 2015
    Wed Apr 01 2015 etc

    It is only when I group it that the date format drops to dd mmm ie 1 Apr

    Any way I can change it so when it is grouped month day that it shows dates as
    ddd mmm dd yyyyy
    Tue Mar 31 2015
    Wed Apr 01 2015

    This is after I removed all the times from the data and only enter mm/dd/yyyy and set the format to show ddd mmm dd yyyyy and it does display correctly. The pivot table without grouping shows correctly. As soon as I apply any grouping it only will display
    d-mmm ie 1-Apr even though the format applied in the pivot table shows
    ddd mmm dd yyyy.

    So this must be a "thing" with Excel any work arounds?
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1145771

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice