4 ms·
> Convert a continuous string of letters and numbers to a date. This has been a massive bugbear of mine. Particularly when it inexplicably chooses USA date for
by stirlo 3y ago
> Convert a continuous string of letters and numbers to a date.
This has been a massive bugbear of mine. Particularly when it inexplicably chooses USA date formats even when faced with a column containing values like 15-07-75. It would frequently convert half the values into US date format where possible and leave others like above unconverted.
- rukuu001 3y agoNot just you. Perhaps no other software feature has caused more hours of lost productivity than Excel auto-formatting whatever to a date. Edit: I wonder, does '1-1' count as a 'continuous string of letters and numbers'? I still don't want '1-1' to be converted to a date.
- n4r9 3y agoYes, I had big issues with Excel converting a number range like "1-3" to "3rd January" when importing property data. Eventually switched to Libre Office which works much more intuitively.
- jimmySixDOF 3y agoOne of the worst things is trying to work with hours as a value in excel it will convert them into time and dates and mess up in csv. Avoid.
- barbazoo 3y agoExcel isn’t a csv editor I’d say. Better to use a text editor.
- mc32 3y agoThe other one is converting large or long strings of numbers into scientific notation…
- zrobotics 3y agoAnd leading zeros, which is awesome when postal codes have them and shipping software uses csv imports. Somebody only has to open the file once, and it isn't immediately obvious unless people know to expect that.
- filcuk 3y agoCSVs need an option to open as text. Or at least to tell people "hey, there butchered your data for ya" with an undo option. I got a ticket from IT dept (lol) that a CSV is the wrong format. It wasn't.
- mthoms 3y agoThis article says the user will be notified if any data is automatically converted when opening a CSV. This should have been done a decade ago but I'm still glad to see it.
- ozyschmozy 3y ago> When you select the When loading a .csv file or similar file, notify me of any automatic number conversions check box, From what I can tell, these are all opt-in via settings. So it won't stop unaware users from accidentally messing up csvs
- david422 3y agoI used to work with some data that would come across as CSV files but from non-technical people and required leading zeros on some fields. Trying to explain the difference between csv and excel ... or why you should not open those files in excel ... was difficult to say the least.
- Eddy_Viscosity2 3y agoUS data formats, REGARDLESS of any regional setting you might have for dates. So even if they were dates being auto-formatted, they were still doing it wrong. Anyone done this? Open a .csv in excel to fix/edit a item, then save it without realizing that it autoformatted a bunch of columns. Now it doesn't work in the parent program anymore.
- mission_failed 3y agoExcel isn't the only MS product with US date insanity. Outlook set up for AU region only supports date filtering in searches using US date formats, which is the icing on the turd sandwich of date searching as Outlook requires you to hand write in text search queries to search for before/after x date.