Forum Discussion

BrianR1805's avatar
BrianR1805
Copper Contributor
Aug 14, 2024

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-...
excel
  • JKPieterse's avatar
    JKPieterse
    Aug 15, 2024

    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's avatar
JKPieterse
Silver Contributor
Aug 14, 2024

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. 

  • BrianR1805's avatar
    BrianR1805
    Copper Contributor
    Aug 14, 2024

    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?

    • mathetes's avatar
      mathetes
      Silver Contributor
      Aug 14, 2024

      BrianR1805 

      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.

       

      • BrianR1805's avatar
        BrianR1805
        Copper Contributor
        Aug 14, 2024

        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.

Resources