11 ms·

Does DATEVALUE not solve this?

• unsupp0rted 2y ago
  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.
  • maxerickson 2y ago
    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.
  • elmer007 2y ago
    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.
  • m16ghost 2y ago
    > (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.