Forum Discussion
How to get excel to sort 2-number dates correctly
Thanks to mathetes for expanding on the discussion and clarifying our needs.
BrianR1805 next time make sure your example data contains as many variations as you expect there are. It helps us spotting the exceptions.
I believe this formula should do it:
=LET( DashPos,FIND("-",A1), lead,LEFT(A1,DashPos-3), yr,MID(A1,DashPos-2,2), trailing,MID(A1,DashPos+1,LEN(A1)), lead&IFERROR(IF(yr*1>30,"19"&yr,"20"&yr),yr)&"-"&trailing)
JKPieterse The formula worked perfectly on my test spreadsheet. Here's the issue I'm just realizing. Some of my Item ID's don't have two-digit numbers like this one (ADAT-0001). Can you add to the formula so that it ignores items without two digit dates but is still able to sort correctly? Thanks!
Oh, and just to put it out there, some of my items have even further numerical divisions so they look like this:
AE99-0173
AE99-0174_001
AE99-0174_002
AE99-0174_003
AE99-0175
etc.
Is that going to mess with your formula?
Some of my Item ID's don't have two-digit numbers like this one (ADAT-0001). Can you add to the formula so that it ignores items without two digit dates but is still able to sort correctly? Thanks!
It always helps to have the full picture at the start. I'm not JKPieterse but I am going to anticipate a question he'll probably have. WHERE in the sort would you put ADAT-0001 when sorting along with (presumably) ADAT99- and ADAT00- and so forth? You say "ignore" but you also want it sorted correctly, and ignoring isn't a way to get to correct sorting. So, on behalf of JKPieterse , please give the full picture. Maybe even a sample file with full examples of the raw data you're dealing with AND how you expect the formula to interpret "correct sorting" when the code is missing the two digits.
Oh, and just to put it out there, some of my items have even further numerical divisions
For the record, his formula would still work with those trailing extra numerical digits. It's the ones that don't have the two year digits that you need to explain more fully.
mathetes I can’t currently provide any sample documents because I don’t go back to work til Friday. Fortunately, there are almost no examples of IDs with both 2-number dates and no date. In those rare cases I would sort the no-date IDs first before any of the 2-number dates. I still want everything to be sorted by ID FIRST, then by two-number date, then trailing numbers afterward.
Thanks to mathetes for expanding on the discussion and clarifying our needs.
BrianR1805 next time make sure your example data contains as many variations as you expect there are. It helps us spotting the exceptions.
I believe this formula should do it:
=LET( DashPos,FIND("-",A1), lead,LEFT(A1,DashPos-3), yr,MID(A1,DashPos-2,2), trailing,MID(A1,DashPos+1,LEN(A1)), lead&IFERROR(IF(yr*1>30,"19"&yr,"20"&yr),yr)&"-"&trailing)- It works perfectly, thanks so much! I'll try to be more specific up front next time I have an issue.
OK, That answers the question but it hadn't been clear before you stated that. One learning point regarding programming in general: you need to be able to state in clear English (or whatever language is one's native language) what is to be done before you can program it, whether in Excel or any other system. It's easy to take for granted what is clear to you, but really an assumption that hasn't been articulated.
I'll leave it to JKPieterse to modify his excellent answer to the opening description of the need.