locked
Excel 2016 Pivot Table Date Display RRS feed

  • Question

  • When creating a pivot table with Excel 2016 and a field has a date of 1/1/16 it adds Years and Quarters fields and the Invoice Date field is blank.  When I take the Years and Quarters fields off of the pivot table the Invoice date displays as Jan.  I need the Invoice Date to display as 1/1/16 like it did in previous versions of Excel.  What steps do I need to take to make this happen?
    Monday, March 14, 2016 5:28 AM

Answers

  • If you select and right click on any of the cells with the years, quarters, or your 'date' field, then select Ungroup, it should revert to a standard date format and only your 'date' field will be displayed. Then you can format the date as you need.

    Just tried on a data set and it worked. Hopefully will work for you.

    Thursday, March 17, 2016 12:47 AM
  • I just right clicked on the cells, chose ungroup and it worked!  Thanks!!
    Wednesday, April 6, 2016 7:41 PM

All replies

  • Maybe you need to add a screenshot to explain what you mean.

    You can always try to select the date cell in question and press Ctrl-1 to open the formatting options. Select Date and then the desired format you want.


    Best regards George


    Monday, March 14, 2016 10:32 AM
  • I'm seeing the same issue. I cannot find the setting that allows me to see just the simple date value.

    Here's what I'm seeing in the pivot.

    Years Quarters Contract Signed Date Time
    2015 Qtr3 Sep

    If I double-click in a total field, the detail behind this shows as the valid date. This appears to be a setting in the pivot, and it is not what I need to see!

    Contract Signed Date Time
    9/9/2015 7:00

    Tuesday, March 15, 2016 7:39 PM
  • If you select and right click on any of the cells with the years, quarters, or your 'date' field, then select Ungroup, it should revert to a standard date format and only your 'date' field will be displayed. Then you can format the date as you need.

    Just tried on a data set and it worked. Hopefully will work for you.

    Thursday, March 17, 2016 12:47 AM
  • I just right clicked on the cells, chose ungroup and it worked!  Thanks!!
    Wednesday, April 6, 2016 7:41 PM
  • Is there ANY way to set the DEFAULT for ALL my pivot tables, in ALL Excel 2016 files, to UNGROUP for date fields?

    I do a large number of pivot tables which include dates and being able to have 2016 ALWAYS create them with the actual Date, or DateTime, data would be MUCH more preferable.

    Friday, June 24, 2016 7:52 PM
  • Yes, there is, finally!

    https://support.office.com/en-us/article/Turn-off-time-grouping-in-PivotTables-in-Excel-2016-for-Windows-6be5afed-348c-4db2-9f87-5ac262d67b3f

    To turn off time grouping on PivotTables (including data model PivotTables) and Pivot Charts, follow these instructions for adding a new DWORD (32-bit) Value registry key. The new key is: HKEY_CURRENT_USER > Software > Microsoft > Office > 16.0 > Excel > Options > DateAutoGroupingDisabled.
    • Proposed as answer by SelimG Thursday, November 9, 2017 8:59 AM
    • Unproposed as answer by SelimG Thursday, November 9, 2017 8:59 AM
    Wednesday, July 20, 2016 9:36 AM
  • Thanks!
    Thursday, July 21, 2016 3:57 AM
  • https://support.office.com/en-us/article/Turn-off-time-grouping-in-PivotTables-in-Excel-2016-for-Windows-6be5afed-348c-4db2-9f87-5ac262d67b3f?ui=en-US&rs=en-US&ad=US 
    Saturday, October 8, 2016 8:42 PM
  • This has bugged me for months! Thanks!
    Wednesday, October 12, 2016 4:04 PM
  • Thank you so much!!! I was going crazy trying to figure this out :)
    Friday, January 27, 2017 8:41 PM
  • Hi MicheleAH

    I am meeting the same issue, do you have answer? please help me.

    Monday, March 20, 2017 4:04 AM
  • Thanks.  What a stupid auto feature upgrade.  Glad they think they know what we are thinking and want.
    Wednesday, May 3, 2017 12:13 AM
  • wow ---- that is another stupid feature. at least they could have just put it in the options master menu.
    Wednesday, May 3, 2017 12:14 AM
  • Thanks!
    Monday, August 7, 2017 6:28 PM
  • This works great if you do not wish to update your pivot data. My scenario uses the pivot as a template and the data is updated regularly. With passage of time, when more data is added and the number of date columns increase, the custom date formatting goes away. Is there any way that all date columns are formatted all by themselves?
    • Edited by gupta.rahul Wednesday, August 9, 2017 11:27 AM
    Wednesday, August 9, 2017 11:27 AM
  • You can actually turn the automatic date grouping function off in the menu Options | Advanced | Data | check the box "Disable automatic grouping of Date/Time columns in Pivot Tables.

    Thursday, September 28, 2017 11:47 PM
  • Thank you for this tip!! 
    Thursday, October 19, 2017 7:24 PM
  • Once the dates are ungrouped, I find the drop-down for the dates in the Pivot Table show each date individually rather than by year (with a "+" to expand).

    When there is a long list of various dates, this becomes very inefficient.  Is there a way to revert back to the condensed view in the drop-down?

    Tuesday, November 7, 2017 5:36 PM
  • Thank you. This does, in fact, work.
    Monday, November 13, 2017 7:12 PM
  • I've been struggling with this for hours until I found this! Thank you so much!
    Thursday, December 7, 2017 3:41 PM
  • Hi JohnQuiz

    I looked in the Options to disable the auto grouping as you suggested but it did not give me the advanced options. Can you please send a screenshot to show the location of this function?

    I a bit of an Excel newby and this seems the best approach for me.

    Thank you.

    Monday, December 18, 2017 1:13 AM
  • That worked - Thank you!
    Monday, February 26, 2018 4:57 PM
  • Any ideas on how to do it on MAC?
    Friday, March 16, 2018 3:47 AM