Sorting Dates in Pivot Table Issues

Question

I have a field called "Calendar Date" which has values that spans across 2 years.

It is formatted as a Date with the "mm/dd/yyyy" format and the formula used to generate this is derived from

=text(A2, "mm/dd/yyyy")

where A2 is in a date format that has hours, minutes, and seconds attached to it. I solely want the date and use the "calendar Date" column to convert from mm/dd/yyyy hh:mm:ss to mm/dd/yyyy.

My issue is that whenever I sort the calendar date, the sort option shows up as "Z to A" or "A to Z"... indicating that it is formatted as a number. It does not sort properly. Even though the value of "calendar date" is clearly in a date format.

What should I do? I have tried several things, but nothing seems to work.

I have checked the formatting of all dates and made sure everything was formatted as a date. The original date column seems to be formatted correctly because when I drag it into the rows fields, 3 fields pop up (year, QTR, and Date). I would use this column instead of "Calendar Date", but the date field is sorted by month and not by the individual days.

Pivot Table View after Inserting Original Date (no further breakdown of months into the days of the month):

Answer 1

Here's a workaround to the powerpivot sorting dates as text (instead of dates): use the "original date", that gets grouped by your pivot table by year, quarter and month :

1. right-click on whatever month (or year, or quarter), select group screenshot
2. Add "days" to your selection screenshot
3. In the creation contextual ribbon, select Report layout, select a tabular one screenshot
4. Basically that's it, you may remove the month subtotal by right-clicking of of them escreenshot

Final result screenshot:

Answer 2

You have both, date values and text string values:

• Your original data values clearly are date values, as you proved by your pivottable screenshot.
  They are shown as indented individual years, quarters, months.

• As you converted them into your "Calendar Date" by =TEXT(...),
  each result is a text string and not a date value anymore.
  It looks like a date value, but Excel doesn't recognize it as date.
  Therefore the result after sorting "A to Z" is useless.

Solution: There is no need for an additional conversion.

1. Use your original date values exactly like shown in your screenshot
2. Do a right mouseclick on one of its items and select "Ungroup ...".
   Then they are not "grouped" as indented individual years, quarters, months (and/or days, hours, ...), but shown as sortable(!) date values.
3. If they show minutes and/or seconds and you don't want that, then select the "Field Settings ..." and change its "Number Format" to a predefined or customized one.