• deweydecibel@lemmy.world
      link
      fedilink
      English
      arrow-up
      56
      ·
      edit-2
      7 months ago

      Honestly, the automatic datetime conversion is the worst part if you’re just trying to keep it text. It’s idiotic there isn’t a simple way to turn off that off. That’s not formatting, that’s actually changing the data in those cells which may not be what you want.

      • Identity3000@lemmy.world
        link
        fedilink
        arrow-up
        25
        ·
        edit-2
        8 months ago

        In case you’re not aware, the latest version of Excel absolutely DOES have that setting (mentioned elsewhere here in the comments). While it’s wild that it took so long, it’s now a solvable problem and everyone should know about it (and upgrade)!

    • flambonkscious@sh.itjust.works
      link
      fedilink
      English
      arrow-up
      18
      ·
      8 months ago

      Holy crap, that’s amazing - I can’t believe it would take this long! I presume straight up numbers are no longer affected, too, but I’m almost willing to trust it and try it out…

      • festus@lemmy.ca
        link
        fedilink
        English
        arrow-up
        6
        ·
        8 months ago

        Yeah it stops converting numbers too. At my job we have a lot of ids that start with 0, and it was super annoying to have ‘000123’ turn into ‘123’, now it keeps it as text.

    • wizardbeard@lemmy.dbzer0.com
      link
      fedilink
      English
      arrow-up
      1
      ·
      8 months ago

      Instead of just opening them with Excel, make a blank workbook or sheet, go to the data tab, import from text/csv. It’ll give you a bunch of menus and such where you can adnust how it reads the file and how it will format columns. Any column with leading zeros you want as text. It doesn’t fuck with “as text” columns.

      The only caveat is that once you’ve imported the CSV that way, you’ll want to open the data sources tab and delete the connection to the csv file, otherwise changes in the original csv file will change shit in your spreadsheet, but only if excel feels like syncing it. Better to shut that off and do it yourself as needed.

  • Blackmist@feddit.uk
    link
    fedilink
    English
    arrow-up
    20
    ·
    8 months ago

    Nice barcode you have there. Be a shame if somebody took any leading zeroes off.

    Nice Int64 you have there. Be a shame if somebody rounded it to 14 significant digits.

  • Omega_Haxors@lemmy.ml
    link
    fedilink
    English
    arrow-up
    12
    arrow-down
    1
    ·
    edit-2
    8 months ago

    Openoffice does this too, and the worst part is that it will take something and interpret it as a date, but then you force it to interpret it as a number, instead of using the number you typed it will use the index number of the date which is a completely different and useless value.

    In short, it takes the number you put in and converts it into a date against your will, and you can’t change it back. It would be like if you typed in “sunshine” and it interpreted that as the temperature of a sunbeam and put that in the cell instead of the fucking thing you typed. So annoying!!

    Another minor gripe is how their Pi() uses enough digits that it lands on a 5 and rounds up, which breaks all sorts of sine math. If they had rounded down or added/removed a digit of pi that wouldn’t be an issue. I have to intentionally add error to my functions just so they work. On the up side I was able to provide mathematical proof that the human tendency to round up at 5 is at odds with the real world. It makes more sense to round down, though I always just add/remove a decimal point so I don’t have add arbitrary information to the system by always assuming a roundup or rounddown. If I’m forced to end at a 5, I round down, which is at odds with how people do it but it breaks less.

    EDIT: come to think of it, it makes a lot of sense for 5 to be a round-down situation. If a particle has just enough energy to jump to the next state but no extra, it would be far more likely to stay in its current energy state than it would be to expend energy to make that jump. Even an infinitesimally small increase over the exact amount required to make a jump would give it that necessary kick to actually make the jump.

    • Ephera@lemmy.ml
      link
      fedilink
      arrow-up
      15
      ·
      8 months ago

      I don’t know, whether these things have been fixed by now, but you should mind that OpenOffice’s development has been basically dead since 2010. All the core devs moved over to LibreOffice, basically because Oracle had bought Sun Microsystems, who previously held the “OpenOffice” brand. You really want to be using LibreOffice these days.

      • Omega_Haxors@lemmy.ml
        link
        fedilink
        English
        arrow-up
        8
        ·
        edit-2
        8 months ago

        That explains why I haven’t got a response from them in over three years.

        EDIT: I’ve downloaded LibreOffice. Their Pi() uses 3.14159265358979 which is an awesome number to end it on.

    • bstix@feddit.dk
      link
      fedilink
      English
      arrow-up
      1
      ·
      edit-2
      8 months ago

      Can’t you just type in the digits of the Pi that you want to use in some cell and then refer to that cell instead of using the default Pi?

      5 is mathematically a “round-up” number. If you want your numbers to behave differently I think you’d be better off using a different function.

      • Omega_Haxors@lemmy.ml
        link
        fedilink
        English
        arrow-up
        2
        arrow-down
        1
        ·
        edit-2
        8 months ago

        Five isn’t mathematically a round-up number, we just arbitrarily decided to round up because numbers were used in merchant contexts where obviously you want to get people to pay more for their stuff than less. As for solutions I just introduce a tiny error because it’s genuinely less effort than constantly referencing a cell and locking it so that it won’t run away if you drag it around. It’s a very easy to forget a lock.

        • bstix@feddit.dk
          link
          fedilink
          English
          arrow-up
          1
          ·
          8 months ago

          Name the cell “myPi” and use that instead of the Pi function.

          • Omega_Haxors@lemmy.ml
            link
            fedilink
            English
            arrow-up
            2
            arrow-down
            1
            ·
            8 months ago

            That’s a pretty high-q move. I’ve moved to LibreOffice which does Pi properly, but i’ll keep that in mind next time I need a static data reference.

    • xpinchx@lemmy.world
      link
      fedilink
      arrow-up
      5
      ·
      8 months ago

      Unrelated but alt+e+s+v is so ingrained into my muscle memory. I should really get a macro pad

    • droans@lemmy.world
      link
      fedilink
      arrow-up
      4
      ·
      8 months ago

      Ctrl+Shift+V exists now which does the exact same thing.

      Actually it’s a bit better. Excel always ignores alt codes for the first key press or two after switching windows.

    • Agent641@lemmy.worldOP
      link
      fedilink
      arrow-up
      4
      ·
      8 months ago

      Programmers who have to deal with data I/O and SQL regularly need to use a spreadsheet to examine, columnize and validate data manually, so that data structures are confirmed to be correct. Nobodys programming in Excel per se, but its still a ubiquitous tool that we use on the regular.