Re:The Microsoft Excel superstars throw down in Vegas
by hot_gril on Friday June 14, 2024 @03:11PM
(#40683965)
Does DATEVALUE not solve this?
[ Parent
| on HN ]
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.