Forum Discussion

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

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)
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.

JKPieterse's avatar
JKPieterse
Silver Contributor
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)
  • BrianR1805's avatar
    BrianR1805
    Copper Contributor
    Aug 16, 2024
    It works perfectly, thanks so much! I'll try to be more specific up front next time I have an issue.

Resources