4 ms·

The scientific notation ("2.02308E+13") is just how Excel displays a large enough numeric value like your example; it's not doing any conversion, as you can see

The scientific notation ("2.02308E+13") is just how Excel displays a large enough numeric value like your example; it's not doing any conversion, as you can see if you paste that number into Excel, confirm that it's showing the scientific notation, and then click into the cell to confirm that the formula bar still shows the original number (20230822090811). 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...

sixothree 3y ago
Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. What are you doing here?? 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?
- DandyDev 3y ago
  My my, why so angry? You're expecting something that Excel doesn't support for good reason. The string you used as an example might be a date string to YOU, but to Excel and many of its users it's just a large integer. Lots of regular use cases would break if Excel would interpret that string as a datwle If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
  - mulmen 3y ago
    It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. The anger is warranted. The problem isn’t assuming a display format for data, it is that a poor assumption actually mangles data. Excel doesn’t fail safe. 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.
    - dragonwriter 3y ago
      > It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. 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.
      - deleted 3y ago
        [deleted]
      - deleted 3y ago
        [deleted]
      - deleted 3y ago
        [deleted]
  - CyberDildonics 3y ago
    My my, why so angry? Because they have been dealing with the same bug for 10 years, I thought that was obvious.
- dragonwriter 3y ago
  > Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. 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-How-to-prevent-Excel-from-converting-your-UPCs-or-GTINs-into-scientific-notation https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
  - deleted 3y ago
    [deleted]
  - mulmen 3y ago
    The text import wizard does work. The problem with it is that everyone has to use it and if anyone doesn’t then your data is silently mangled. 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.
  - Alupis 3y ago
    I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... 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.
mulmen 3y ago
Excel only maintains 15 digits of precision for numbers. Everything else gets truncated. This truncation is done at import so it is unrecoverable. 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.
ooterness 3y ago
Except Excel doesn't recognize ISO timestamps either.
- mulmen 3y ago
  Are you sure? It seems to for me, which is annoying because by recognizing them it changes them to a localized date format which then breaks when sent overseas. 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.
  - ooterness 3y ago
    Definitely broken for me on Excel version 2302 (build 16130.20714) on an En/US locale. 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.
    - mulmen 3y ago
      I am En/US but on MacOS.

sixothree 3y ago

Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. What are you doing here?? 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?

DandyDev 3y ago
My my, why so angry? You're expecting something that Excel doesn't support for good reason. The string you used as an example might be a date string to YOU, but to Excel and many of its users it's just a large integer. Lots of regular use cases would break if Excel would interpret that string as a datwle If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
- mulmen 3y ago
  It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. The anger is warranted. The problem isn’t assuming a display format for data, it is that a poor assumption actually mangles data. Excel doesn’t fail safe. 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.
  - dragonwriter 3y ago
    > It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. 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.
    - deleted 3y ago
      [deleted]
    - deleted 3y ago
      [deleted]
    - deleted 3y ago
      [deleted]
- CyberDildonics 3y ago
  My my, why so angry? Because they have been dealing with the same bug for 10 years, I thought that was obvious.
dragonwriter 3y ago
> Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. 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-How-to-prevent-Excel-from-converting-your-UPCs-or-GTINs-into-scientific-notation https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
- deleted 3y ago
  [deleted]
- mulmen 3y ago
  The text import wizard does work. The problem with it is that everyone has to use it and if anyone doesn’t then your data is silently mangled. 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.
- Alupis 3y ago
  I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... 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.

mulmen 3y ago

Excel only maintains 15 digits of precision for numbers. Everything else gets truncated. This truncation is done at import so it is unrecoverable. 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.

ooterness 3y ago

Except Excel doesn't recognize ISO timestamps either.

mulmen 3y ago
Are you sure? It seems to for me, which is annoying because by recognizing them it changes them to a localized date format which then breaks when sent overseas. 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.
- ooterness 3y ago
  Definitely broken for me on Excel version 2302 (build 16130.20714) on an En/US locale. 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.
  - mulmen 3y ago
    I am En/US but on MacOS.