Aug 25 2023 06:22 AM
Aug 27 2023 12:03 PM
@Amitfre1With General-format cells, Excel looks at the user input for each cell (including a pasted value) and tries to determine if the input is a formula, a date value, a time value, a Boolean value, a percentage, a currency amount, or some other number*. If Excel decides it is any of those, it stores the value differently than the input characters. So some of your Case Number values looked like they were dates (in the YYYY-MM-DD format) and Excel stored them as dates, and displayed them in the date format it felt was appropriate based on your regional settings, etc.
The correction will involve two of these three steps:
* I might be forgetting something in this list. Not important now.
Aug 28 2023 03:09 AM - edited Sep 02 2023 12:51 AM
@Amitfre1 MS has worked on the issue of Automatic Data Conversion (ADC). In my Excel Insider version I have a set of options that allow me to choose what and what not to convert under Excel Options, Data.
It used to be labeled "BETA" and it was part of the Advanced options. I haven't seen an announcement on this feature (perhaps I missed it). but it could be that it is now generally available in Excel365.
If not, consider using Power Query to connect to the data that you scraped from the web. Then you take control over data conversion (or transformation in PQ terms).
Edit Sep 2, 2023:
I now found the note I was looking for earlier. The ADC option is still for Windows Insiders only (Version 2207 [Build 15427.20000] or later)
Aug 29 2023 01:52 PM
I wasn't able to find Automatic Data Conversion in Advanced Options, yet any changes to my date entries automatically converted. However, the very bottom of Advanced Options has two boxes for Lotus compatibility settings. They were checked. I unchecked them and the problem was resolved.
Aug 29 2023 11:01 PM
@rhoneyman If the Automatic Data Conversion (ADC) options are generally available already (which might not be), they sit under Excel options , Data. When this feature was in the beta phase they where stashed away in Advanced options.
I'm aware of the existence of the Lotus compatibility settings but not of the impact they may have on ADC. I don't recall that these were ever checked in my set-up and Excel persisted to automatically convert data upon import, before I could select the new ADC options.
By the way, we are talking about data conversion here, not just 'date entries being converted'. That is, dropping leading zeroes, transforming very large numbers or digits surrounding the letter E into scientific notation, or for example converting a text like "18/04/5405" to a date.