News for hackers. Stuff that matters.
Re:The Microsoft Excel superstars throw down in Vegas by unsupp0rted on Friday June 14, 2024 @03:15PM (#40684012)
Nope. DATEVALUE fumbles this badly.

It's precisely for parsing dates but on a column with 1-digit vs. 2-digit days/months and 2-digit vs. 4-digit years (all in d/m/y format, mind you), it fails in one instance or the other.

Re:The Microsoft Excel superstars throw down in Vegas by maxerickson on Friday June 14, 2024 @04:22PM (#40684707)
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.

Re:The Microsoft Excel superstars throw down in Vegas by elmer007 on Friday June 14, 2024 @05:06PM (#40685128)
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.
Re:The Microsoft Excel superstars throw down in Vegas by m16ghost on Friday June 14, 2024 @05:08PM (#40685151)
> (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.