Numbers will categorize imported data as a date if the cell value matches a recognizable pattern. In this case it just looks like 5- or 6-digit numbers, and nothing to indicate a date, so it leaves it as a Numeric cell.
Parsing the value shouldn't be a problem though. The only tricky thing is that the month is either one or two digits, but that can be handled. Here's a couple of examples of how to do it. Assuming the data is in column A, set B2 to:
text parsing (character position in the string):
=LET(yy,2000+RIGHT($A2,2),
dd,MID($A2,LEN($A2)−3,2),
mm,LEFT($A2,LEN($A2)−4),
DATE(yy,mm,dd))
Numeric parsing:
=LET(yy,2000+MOD($A2,100),
dd,QUOTIENT(MOD($A2,10000),100),
mm,QUOTIENT($A2,10000),
DATE(yy,mm,dd))
The first method (text parsing) relies on counting the number of characters in the string (either 5 or 6) and breaking the string into three parts, labelled yy, mm, and dd. These values can then be passed into the DATE() function to return a valid date value.
The second method is similar, but uses numerical processing to divide the source value by various factors to come up with the same yy, mm, dd breakdown for the DATE() function.
Note that I've used the LET() function to make it clearer, but this will only work on Numbers 14.4 and later. This shouldn't be a problem if you're on MacOS 26, but just in case you can fall back to the 'traditional' model where the parsing is done inline with the DATE() command:
=DATE(2000+MOD($A2,100),QUOTIENT($A2,10000),QUOTIENT(MOD($A2,10000),100))
