98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Date Field being split in Pivot Tables
Date Field being split in Pivot Tables
Resolved · High Priority · Version 2016
Wendy has attended:
Excel Introduction course
Date Field being split in Pivot Tables
I am encountering problems with Pivot Tables not maintaining the Date Format as it appears in the sheet I am pivoting. For example, my old version of excel may have a report containing data for the 6 month period ending in June or December, this would be shown in one cell on my report as either "Jun-2017" or "Dec-2017 (or sometimes as "30-Jun-2017" and "31-Dec-2017". If pivoting this data by period the pivot used to present the Date in one cell (just as it is shown on my report). Now, since a change in laptop and updated version of excel, my pivot wants to break the date into parts and I need the date to be reflected in my Pivot Table just as it is in my report (in one cell).
Any ideas on how I can make my pivot tables maintain the date in one cell, just as it is shown in my report? Formatting cells has been tried and this has not resolved the problem.
Your help is much appreciated.
Thanks
Wendy
RE: Date Field being split in Pivot Tables
Hi Wendy,
Thank you for the forum question.
Yes I find it also annoying that Excel 2016 group pivottable dates in year, quarter, and month but it is easy to change. Select one of the years and on the Analyse tab click Group Field in the Group group. On the list deselect Year, month, & quarters and select dates.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Date Field being split in Pivot Tables
Hi Jens
Thank you so much! I have never used this Analyse tab and this is going to be very useful for every day work. I tried the Group Field setting, and whilst I could see it changing the fields it didn't quite get my data set to how I wanted it, however, when I clicked on the option above ("UnGroup") that has done the trick!
Can you please advise if there is a way to make this always the default setting in Excel 2016 or is it something I'm going to have to do every time I need the date maintained as it was in the report?
Thanks
Wendy
RE: Date Field being split in Pivot Tables
Hi Wendy,
Sorry unfortunately this is not possible.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlinesExcel 2010 allows you to change the colour of grid lines instead of keeping them in boring black. |