> (all in d/m/y format, mind you)

The examples you gave are in m/d/y format though, and DATEVALUE() parses your examples correctly into Jan 15th, 2024 and January 3rd, 2024.

DATEVALUE() parses ambiguous short date formats (e.g. 1/3/24) using the short date format specified in the Region settings of Windows Control Panel. So if you want to parse d/m/y format, you can try changing the settings there.

I just put all 6 of the examples above into Excel as text, then used DATEVALUE to parse them. It worked correctly for all 6. However, in the immediate parent comment, you mention d/m/y format, which 3 of the examples above could not be, so perhaps the details of the issue you've encountered are more subtle (such as 2-digit years that require context to determine the century); but, at present, it looks like DATEVALUE handles this well.

Text to columns with a date value? (it works fine if it's one columns)

I expect it gets your example there right, but you may have other issues in mind that you didn't push into the example.