6 ms·

Can you just please fix the issue with date strings (such as "20230822090811") being converted into scientific notation? I've received literally hundreds of the

Can you just please fix the issue with date strings (such as "20230822090811") being converted into scientific notation? I've received literally hundreds of these documents over the past decade. And as long as this problem exists, I will keep receiving them. Can you please just add a button - "unmess this document"? The number of man-hours I've wasted re-creating documents is countless. My job is to do other things not fix excel documents.

john-radio 3y ago
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.
twobitshifter 3y ago
The way you fix this is using the text import tool or better yet power query and specifying the column type to be text. With power query you can repeat this for as many files as you need to import.
- dragonwriter 3y ago
  If he's recieving excel files with this issue from other people, the text import tool isn’t a solution and Power Query is a bit circuitous to solve what can be addressed by formatting the range with this kind of data.
rjmunro 3y ago
Excel makes a total mess of many kinds of imports, assuming they are dates when they are not or assuming US vs UK formats dates etc. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... However, to my eyes, and probably most people, 20230822090811 does not look like a "date string", it's a number. It's correct to display it as scientific notation if it's too many digits to fit into the width of a cell. Please let's not encourage them to interpret more things as dates and randomly break people's workflows. Excel should preserve all the digits you enter in the cell even if it doesn't display them, though. That has nothing to do with dates. It's a problem with things like product numbers or keys that can end up with many digits.
- wredue 3y ago
  Are you saying that excel does preserve digits? I must be doing something very wrong (entirely possible), cause once the document is saved, the digits are gone.
  - calfuris 3y ago
    That reads as a normative "should" to me. It doesn't preserve digits, but it ought to.
    - rjmunro 3y ago
      Yes, sorry, that's what I meant. Excel doesn't preserve the digits but it should.
bombcar 3y ago
You may want to try a CSV editor: https://www.moderncsv.com https://www.moderncsv.com - it's helped me already.
- recursive 3y ago
  +1. I just discovered this, and it's happily eliminated at least half my use case for Excel, and does a better job at it too. That is, visualize CSV files without messing with the data.
datavirtue 3y ago
All the crusty broken shit in Excel have become features over the last thirty years.
mthoms 3y ago
Optimist: The glass is half full. Pessimist: The glass is half empty. Excel: The glass is January 2nd.

john-radio 3y ago

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.

twobitshifter 3y ago

The way you fix this is using the text import tool or better yet power query and specifying the column type to be text. With power query you can repeat this for as many files as you need to import.

dragonwriter 3y ago
If he's recieving excel files with this issue from other people, the text import tool isn’t a solution and Power Query is a bit circuitous to solve what can be addressed by formatting the range with this kind of data.

rjmunro 3y ago

Excel makes a total mess of many kinds of imports, assuming they are dates when they are not or assuming US vs UK formats dates etc. https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... However, to my eyes, and probably most people, 20230822090811 does not look like a "date string", it's a number. It's correct to display it as scientific notation if it's too many digits to fit into the width of a cell. Please let's not encourage them to interpret more things as dates and randomly break people's workflows. Excel should preserve all the digits you enter in the cell even if it doesn't display them, though. That has nothing to do with dates. It's a problem with things like product numbers or keys that can end up with many digits.

wredue 3y ago
Are you saying that excel does preserve digits? I must be doing something very wrong (entirely possible), cause once the document is saved, the digits are gone.
- calfuris 3y ago
  That reads as a normative "should" to me. It doesn't preserve digits, but it ought to.
  - rjmunro 3y ago
    Yes, sorry, that's what I meant. Excel doesn't preserve the digits but it should.

bombcar 3y ago

You may want to try a CSV editor: https://www.moderncsv.com https://www.moderncsv.com - it's helped me already.

recursive 3y ago
+1. I just discovered this, and it's happily eliminated at least half my use case for Excel, and does a better job at it too. That is, visualize CSV files without messing with the data.

datavirtue 3y ago

All the crusty broken shit in Excel have become features over the last thirty years.

mthoms 3y ago

Optimist: The glass is half full. Pessimist: The glass is half empty. Excel: The glass is January 2nd.