date field being split

Forum 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

resolvedResolved · 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

RE: Date Field being split in Pivot Tables

OK, good to know. THanks Jens.


 

Excel tip:

Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlines

Excel 2010 allows you to change the colour of grid lines instead of keeping them in boring black.

Select the File tab on the Ribbon, click Options, click Advanced, scroll down to ''Display options for this worksheet.'' Next to ''Gridline colour,'' choose your favourite colour, then once you've done this, click OK. Easy!

View all Excel hints and tips


Server loaded in 0.07 secs.