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