Dates Not Sorting Correctly in Pivot Table

[eightythree]

Here is how my Pivot Table is set up.

\1
\1
\1

The way my dates are derived in the source data comes from an equation that converts the UNIX timestamp in Column C to PST.

=IF(ISBLANK(C2),"",(C2/86400)+25569+($H$1/24))

When I group by day, and attempt to sort, it changes the date format and will not sort correctly. Any ideas here?

[Richard Buttrey]

Hi,

Please upload the workbook rather than pictures - see guidance in rule area. Most of us are reluctant to recreate your workbook when you have one available and you'll receive more responses.

[eightythree]

Ok I attached the worksheet to this post. Thanks for your help in advance!

[eightythree]

Any ideas here?

[Richard Buttrey]

Hi,

The dates appear to be in descending order but without showing the year. The Date column in the table is actually a Date & Time number, i.e. one containing a decimal value. If you wrap the date formula in an INT() function then refresh the PT you will see the year appearing. Is that what you mean?

[eightythree]

The problem is that in the Pivot Table, the dates will not order correctly and the date format (dd-mmm) cannot be changed. The source data includes date and time as you mentioned, so Excel should be able to sort it correctly.

[Kaper]

Don't know what is wrong. I opened the file - indeed dates look like sorted "text way" - descending order 8... 7... 6.... 5... 10... 1 is earlier in alphabet than 5 :-)
but I right clicked on table - choosen "Refresh" and ... magic! It's sorted as dates not as text. So I did not investigated any further.

May be try to refresh your pivot and it works also for you?

[Richard Buttrey]

The problem is that in the Pivot Table, the dates will not order correctly and the date format (dd-mmm) cannot be changed. The source data includes date and time as you mentioned, so Excel should be able to sort it correctly.

Have you tried the solution I offered you in post #5.

As you correctly indicate the source data includes a TIME, and therein I believe lies your problem. A PT can group by Years, Qtrs. Months, but not Hours, and indeed it seems to object to cells which carry a decimal time value when Grouping. Hence my suggestion to use the INT() function.

[eightythree]

I wrapped the formula in the INT() function, then on the data source I set the format to General. So in the source data, the dates appear as numbers. On the Pivot table, I set the formatting to date, and it seems to work. However, when I attempt to group the dates in different segments, IE Months/Days/Weeks, it attempts to group them as numbers not dates anymore. Such a strange bug.

[Richard Buttrey]

Hi

I somehow doubt you're seeing a bug in what's a fairly common use of date numbers and groupings in Pivot Tables. Upload your workbook so that we can take a look and explain what you are expecting to see.

[eightythree]

I attached the updated worksheet.

As you can see, I wrapped the dates in the INT() function on the source data and formatted them as General. The dates now sort correctly and with the correct date format. However, if I attempt to group them, it tries to group by numbers, not by dates.

[Richard Buttrey]

Hi,

Seems the problem is associated with the format chosen for dates. It seems the Grouping functionality is not happy with formats preceded by an * used to ensure dates change according to regional settings.

See attached

[yyl111]

So i had the same problem and i solved it in 2 ways (that one way worked for 3 PT and the other one for the forth one):
1. Changed in the source to be Date as it was General before. This was suggested also before.
2. Ungroup your PT, sort by the ungrouped dates, and then Group again. For some crazy reason it did the trick.

Hope it helped.
Joe