• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date column formatted correctly, but sorts A-Z vs Newest to Oldest

See attached example.
The DATA worksheet holds order data and new orders are added here
The REPORT worksheet index/matches from the DATA worksheet

REPORT worksheet: DATE1 column filter sorts Newest to Oldest.
REPORT worksheet: DATE2 column sorts A-Z.

DATA worksheet: Both columns are in date format and the hierarchy shows dates. If you look at ImageA, I show the filter menu for DATE2 and all looks fine.
REPORT worksheet: Both columns are in date format and both columns are utilizing the same Index/Match formula. However, if you look at ImageB, the DATE2 column shows sorting as A-Z
DATE2 column I tried: Data > Text to Columns > Next > Next > Date > Finish - no luck

I'm stumped.
Thank you in advance for your help.
 

Attachments

  • Dates sort A to Z.xlsx
    21.7 KB · Views: 7
  • ImageA.png
    ImageA.png
    25.2 KB · Views: 8
  • ImageB.png
    ImageB.png
    20 KB · Views: 11
I changed Column A and made it a part of the table so I can sort on the Column in question. You're right it does sort correctly. Just weird that it says A-Z, when the other column is Newest to Oldest. Even if I run Data > Text to Columns it still says A-Z. Oh well, at least it's sorting.
 

Attachments

  • Dates sort A to Z.xlsx
    63.7 KB · Views: 8
Yes that seems weird but at least sorting works as expected. Maybe restart from a new worksheet …​
 
I thought the same thing. The worksheet I shared here was a new worksheet. The original worksheet has proprietary information.
Thank you Marc L!
 
Yodelayheewho
About Your Columns D and F are to show if Excel recognizes the Dates as numbers
... anyway those Your columns ISNUMBER1 and ISNUMBER2
Those are fixed values in Your sample files ... those won't give any valid information.

Why do You try to use any Data > Text to Columns ... if (as) those are already real dates?
 
I did check dates, I did not trust the fixed values but maybe I missed something …​
 
Yodelayheewho
About Your Columns D and F are to show if Excel recognizes the Dates as numbers
... anyway those Your columns ISNUMBER1 and ISNUMBER2
Those are fixed values in Your sample files ... those won't give any valid information.

Why do You try to use any Data > Text to Columns ... if (as) those are already real dates?
Oops! In transferring the data to a 'clean' worksheet, I forgot to reenter the formulas in columns C and E. Attached is the updated sample.
I tried Data > Text to Columns on Column D again and sorting remains A-Z.
Like Marc L said, the column is sorting correctly. It's now just a curiosity on why Excel is doing this.
Thank you all for your input and insight!
 

Attachments

  • Chandoo_Dates sort A to Z.xlsx
    68.6 KB · Views: 1
Per the attached file above, the dates are formatted like m/d/yyyy, which is Excel's short date format. I actually prefer the format mm/dd/yyyy, but found that can create problems, so I surrendered to Excel's short date format.
 
That comes from how the 'blank' cells were created as once all cells are filled with a date number the label is correct …​
 
So I guess you used a formula to create the 'blanks' cells - or some web copy - but in fact some are not blank containing an empty string​
as once 'removed' the issue is vanished like you can see on my table in column G in the below attachment.​
Just compare what happens when hitting the keys combo Ctrl ▼ from cell C2 versus from cell G2 …​
To conclude : according to Excel rules this is very not an issue, totally normal !​
 

Attachments

  • Dates sort A to Z .xlsb
    14.5 KB · Views: 2
Back
Top