If you paste your date string in a more standard date format like 2023-08-220 9:08:11, then Excel will correctly infer that it is a date. However, you seem to be saying that Excel should handle ANY numerical strings that way if they are long enough to express a date or datetime, which, while it might make your particular workflow easier (or it might not), it would really mess with people's ability to use Excel with large numerical values...
This burned me frequently when I was generating tax audit reports for accounting. The billing system IDs were all BIGINTs but had over 20 digits.
I can provide instructions on how to do this until the cows come home. I will still get files with these issues. Is that not obvious?
Do you really think I want to examine _tens of thousands of rows_ in documents that are 100 cells wide looking for a single exponent value!?!?!?! Is that really what you're suggesting as a fix here?
If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
Numbers in Excel only maintain 15 digits of precision. Leading zeros are also truncated. If you haven’t experienced this you are lucky (or just didn’t notice) but it is real.
> If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
You don’t always control the upstream system. Your response can be considered rude because it implies you think you know more than the person expressing pain. This is especially insulting with such a widely known problem in Excel.
This behavior burned me with billing system IDs. Logically they are strings, we would never do math on them. For performance they were implemented as BIGINTs in the DB. Excel truncates everything after the 15th digit. Our IDs were all over 20. So accounting couldn’t reconcile our reports with the billing system.
That's a problem, sure, but not the one sixothree is complaining about.
> The problem isn’t assuming a display format for data
Your problem might not be, but that’s exactly the problem sixothree is complaining about that the post you are responding to addresses, so while your post references a valid complaint, it is a complete non-sequitur as a response.
Because they have been dealing with the same bug for 10 years, I thought that was obvious.
I don’t believe you’ve been doing it that long and haven't found the fix.
Apply appropriate formatting if you have long strings of numeric digits [as long as they can’t have leading zeroes] that you want stored as numbers but displayed without scientific notation. (Not the best choice for what is semantically a date, in your case, but...that’s a whole bigger issue and, well, babysteps.) You don't have to look at anything, just do it for the ranges where that is the kind of data:
https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
In the past I have added an apostrophe to the front of any integer that should be displayed as a string. Excel will not display that leading single apostrophe. It of course taints that data forever but I consider “Exported to Excel” a terminal state anyway.
It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... it'll wreck every single one every single time, unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc. Try working with a spreadsheet full of database id's... same thing. It's asinine.
By the very nature of Excel, large numbers belong as-is... ie. you're studying something and need the values. Approximations (what scientific notation gives you in the best case) are an exception to what people need nearly every time.
Okay, so set formats appropriate to what is useful for your data.
Defaults don't cover all cases, otherwise there would be no non-default options.
> It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes...
Codes aren’t numbers they are strings, even if the code is a sequence of digits. They should be formatted as text. For actual numbers that shouldn't be displayed in scientific missions, the appropriate numeric format does that.
> it'll wreck every single one every single time
If its any code that can be safely stored as a number, it won't wreck it. It’ll just look bad until its fixed. If it, say, has significant leading zerores, yes, it’ll wreck it, because excel defaults to dealing with numbers, not text, and there is a difference between text made up of digits and actual numbers.
> unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc
Knowing the shape of data you are entering and selecting a column and applying an appropriate format (including “text” as an option) before you enter any data (usually, to a whole column) isn’t that arduous.
If I type "2023-08-22" into a cell in Excel I see "8/22/23" in the cell and "8/22/2023" as the value.
None of the ISO8601 formats I've tried are recognized. "2023-08-22", "2023-08-22T01:38:22Z", "20230822T013822Z" are all valid ISO8601, but Excel treats them as unformatted text. Inputs with slashes are instantly recognized as a date.
If it works for you, it may be a locale thing. But getting away from that is the whole point of ISO8601.
