Forum Discussion
How to get excel to sort 2-number dates correctly
I'm working with a spreadsheet with over 28,000 rows of video/audio tape IDs that typically take this format: ID tag / Date (2 numbers) / Number. So, the 3rd Corporate tape from 1998 would be CORP98-0003. I want to sort this spreadsheet by ID tag first then numerically by date. The problem is that Excel automatically sorts the year 2000 (00) before the year 1999 (99) instead of doing it by date. This makes it take SOOO long to manually sort individual groups of NEW tapes into the 28,000-row spreadsheet. It would so much easier and faster if I could get Excel to automatically sort tapes my way. Just to be clear, I would like it to sort tapes this way:
CORP99-0099
CORP99-0100
CORP99-0101
CORP00-0001
CORP00-0002
CORP00-0003
etc.
How can I do that? I'm still fairly new to Excel. Thanks!
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)
BrianR1805 Assuming you have Excel 365.
I would use a helper column which extends the year to 4 positions based on a cut-off date. I assumed 30. If your code is in cell A1:
=LET(DashPos,FIND("-",A1),lead,LEFT(A1,DashPos-3),yr,MID(A1,DashPos-2,2),trailing,MID(A1,DashPos+1,LEN(A1)),lead&IF(yr*1>30,"19"&yr,"20"&yr)&"-"&trailing)Then sort on this new column.
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_001AE99-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 divisionsFor 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.
