Excel 2010 Pivot Table Column Labels - Week Ending

[PWD]

Good afternoon, not too hot with Pivot Tables. We have an older workbook that includes data from the previous Financial Year that has a Pivot Table with "Week Ending" as the Column Labels. These go across the worksheet columns in ascending date order.

We now obtain data from a completely different record system so I have attempted to append data to the Source Data - just two weeks at the moment. This appears to have worked but when I change the Source Data and Refresh, the "Week Ending" of the new, appended, data appears before all the original dates.

Any ideas as to what could be going on?

Many thanks,
D€$

 

[SkipVought]

Hi,

In the PT, select the Pivot Field Title representing those dates, Right-click and a pop-up appears. You can SORT, ascending or descending.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, it's 21:15 so I'll check when I'm back at work tomorrow. Thanks.

Many thanks,
D€$

 

[PWD]

Hi Skip, just looking at this again and, for some reason, sorting up OR down results in the new dates being sorted to the other side of the earliest existing dates. I have attached the workbook in case you can spot what I've done.

Many thanks,
D€$

 

[SkipVought]

The problem is not in the PivotTable. Rather its your Source Data. The last data you entered for Birthing Unit.

For some reason the Number Format in the columns Week Ending & Week_Day_Description are different than the previous rows????

Week Ending is formatted d-mmm-yy while your added rows are formated GENERAL. This means that your data, that appears to be dates, is merely TEXT. Consequently, Excel sorts them as TEXT in the PT: first TEXT, then numbers.

If you were to EDIT each cell and hit ENTER, Excel will CONVERT them to real dates. CAVEAT: when I EDIT these cells, Excel makes 08/05/2016 and August date BUT will not convert 15/05/2016 because there's no month 15.

faq68-5827
faq68-7375

So the real question is, how did this data get entered as TEXT? You must have done something out of the ordinary. You need to do some forensic analysis to discover what happened, so it won't happen again.

BTW, after you fix your source data, don't forget to REFRESH your PT.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]