back to article It is 2023 and Excel's reign of date terror might finally be at an end

The days of Microsoft Excel's "helpful" habit of automatically converting values might be at an end as the Windows giant has finally admitted that, yes – not everything is a date. As well as spawning a thousand memes - or so it feels like - Excel's habit of helpfully converting values into types that the user didn't intend has …

  1. cosymart
    Holmes

    Optional

    Wow! You mean we might get some software that treats users as intelligent beings? We don't, really we don't want software that makes assumptions We want options - please.

    1. steelpillow Silver badge

      Re: Optional

      That's why I use LibreOfiice Calc

      1. Michael Wojcik Silver badge

        Re: Optional

        Yes. Now if only I could get everyone else in the company to do the same...

        I utter a curse every time someone asks me to open a spreadsheet. I have always hated Excel, and every time I use it, I loathe it more. It's a festering pile of unhelpful garbage.

    2. elsergiovolador Silver badge

      Re: Optional

      What do you want to do today?

      1. Ken Moorhouse Silver badge
        Trollface

        Re: What do you want to do today?

        Reply to All.

        1. Kane
          Mushroom

          Re: What do you want to do today?

          "Reply to All."

          RELEASE THE KRAKEN MAILSTORM!

      2. Anonymous Coward
        Anonymous Coward

        Re: Optional

        It's more like "what do you want to be messed up today?". Honestly, from a UI that takes multiple steps to do anything and you have to be careful with or you get a popup somewhere (with or without unwanted advertising to boot) to a veritable flood of patches that over the PB that have been pumped through the Net throughout the years SHOULD have eventually yielded something stable but hasn't to randomly changing where everything is with every. new. friggin. version I deem it a testament to ignorance and blackmail that they still manage to flog this rubbish.

        To me it's proof that 98% of the population are secretly masochistic sheep. Well, OK, some are that publicly.

      3. Anonymous Coward
        Anonymous Coward

        Re: Optional

        Close but not quite.

        "What did you want to work today?" was my preferred one.

    3. herman

      Re: Optional

      Next, MS will integrate ChatGPT into Excel and you will looove it!

      1. David 132 Silver badge
        Happy

        Re: Optional

        > Next, MS will integrate ChatGPT into Excel and you will looove it!

        “Your Plastic Pal Cell Mate Who’s Fun To Be With”

    4. hitmouse

      Re: Optional

      The text file import wizard has been around for decades and handles 90% of the issues that people have complained about quite smoothly. I've shown it to a number of people who couldn't be bothered to RTFM and they've generally reacted with "it was that easy????"

      1. Michael Wojcik Silver badge

        Re: Optional

        What does that have to do with Excel's many sins against data?

  2. Doctor Syntax Silver badge

    Interesting. The linked blog kicks off "We have consistently heard from customers over the years".

    They're consistently heard for years but only just got round to listening.

    1. David 132 Silver badge

      “We have consistently heard from customers, but Management reminded us that in fact it was actually ‘screaming from victims’ and could be ignored…”

  3. Andy Non Silver badge

    Sounds like the Access automatic case-conversion "feature"

    An aeon ago, I inherited a rather complex application and associated access database. One of the tables had an index field of lowercase letters a to z. Then an Access update introduced spell chucker and similar features to Access. Unbeknown to me it changed all the lowercase letter 'i's to uppercase 'I's, trashing the index. Only came to light after hundreds of CDs had been shipped to clients.

  4. JessicaRabbit

    Now if only they'd get around to not treating things that look like formulas in csv files as formulas so we don't have to worry about CSV exports that contain a value like =cmd|' /C calc'!A1 in them causing Excel to execute arbitrary commands on the victim's computer.

    1. Headley_Grange Silver badge

      What I've never understood is that it can do all that "intelligent" converting, but if I enter 2+2 it can't work out that it's meant to add them togethether.

      1. Alumoi Silver badge

        Maybe it can't figure out if you're buying or selling.

      2. Ken Moorhouse Silver badge

        Re: but if I enter 2+2 it can't work out

        That begs the question: Did Excel come out the Ark with Noah?

        1. herman

          Re: but if I enter 2+2 it can't work out

          Sortof, Noah’s Abacus had only ten rows and columns.

        2. Boris the Cockroach Silver badge

          Re: but if I enter 2+2 it can't work out

          It was published before the ark was built, in fact Noah's rival Yessuh used it to build his ark. sadly it type converted the measurements into talents rather than cubits and Yessuh's ark could only take 2 ants and Yessuh and his family didnt make it...

          1. David 132 Silver badge
            Happy

            Re: but if I enter 2+2 it can't work out

            Was he a distant ancestor of Bergholt Stuttley (“Bloody Stupid”) Johnson?

      3. David 132 Silver badge

        > …if I enter 2+2 it can't work out that it's meant to add them…

        Obligatory XKCD!

        1. Michael Wojcik Silver badge

          Pretty sure Randall's just describing PHP there.

      4. Zippy´s Sausage Factory

        In theory you should always start formulas with an equals sign. In practice, Excel seems to have some weird, arcane and arbitrary rules around when, how, and if formulas get to be recognised.

        I've found it easier not to think about it. Thinking about it makes my brain hurt, as well as making me sad.

        1. John Brown (no body) Silver badge

          I think that's fair enough. IIRC, VisiCalc, probably the first ever mass market spreadsheet on the Apple ][ and similar devices of the time also started formulae with an = symbol.

          Edit. I google to help my fading memory, and formulae started with + not =. Maybe it was some other spreadsheet I used in the DOS days, maybe Lotus 1-2-3 or SmartWare.

  5. Paul Hovnanian Silver badge

    June Cleaver

    ... thanks you.

  6. steelpillow Silver badge

    the pain caused by its productivity tool

    Yeah, the whole Office suite should have been christened Microsoft Oxymoron from day one.

    1. ecofeco Silver badge

      Re: the pain caused by its productivity tool

      Far too many people talk about the "productivity" of Microsoft (not the just the stans that show up here) that I have to wonder if we are using the same software.

      MS did not get rid of Clippy, it just hid it in every single action you take to get the job at hand, done. I spend more time fighting to turn off the pop-ups and auto-whatevers that productivity goes right out the window.

      And even after turning it all off, it gets turned right back on again after any major updates. Or some new annoying "feature" is added whose control is hidden 3-4 layers down.

      Yeah and updates? Seems like one every week.

      MS has turned just running and maintaining Windows into a full time job. I consider it a good month when I don't have to chase down and kill some annoying "help" feature during that four weeks.

      Productivity? For who?

      And LOL, how about that Bitlocker boat anchor? Kills 50% performance on your SSD.

  7. Ken Moorhouse Silver badge

    which only applies from 2309

    We will all be dead by then.

    Oh? So that's not a date then? /sarc

  8. Ken Moorhouse Silver badge

    "To that end, an Automatic Data Conversion section has been added"

    Noooo, please nooooo.

    You've already caused enough mess.

    Can you just imagine what it will do? There's only one thing worse than allowing Microsoft to do anything Automatically, and that is using AI to do so.

  9. FirstTangoInParis Bronze badge

    Behold the 2039 problem

    Add a date in Excel 2/3/31 and Excel will lovingly convert that to 2 March (yes YMMV) 1931 …. not 2031 like you wanted in your working days calculator sheet. Who was using Excel back then? Some time traveller?

    1. Aleph0

      Re: Behold the 2039 problem

      There are people born in that 30's that are still alive, and I guess more users have to input birth dates than bond/mortgage maturities...

      BTW I'm pretty sure Excel has a setting for the century when the user inputs two-digit years, you may want to look into it. I recall having to tweak it on each reinstall, back when I used to work with securities on version 4 some thirty years ago...

    2. CowHorseFrog Silver badge

      Re: Behold the 2039 problem

      TYpical arsewipe everything is about them... they have no empathy that others have other use cases. As an asswipe its just too difficult for a lazy arse to type a year in full aka four digits.

  10. Kev99 Silver badge

    Just precede the entry with an apostrophe ( ' ).

    1. Joe W Silver badge

      how?

      I mostly import data from files or need to copy stuff from other programs - the last time was copying and pasting from SQL Server Management Studio into Excel.... did not work. Half of the numerical columns got converted correctly, half didn't. No clue why...

    2. Richard Cranium

      After the "fix" input 0123 and a little warning triangle pops up reading "The number in this cell is formatted as text or preceded by an apostrophe" and it is indeed text, you can't do mathematics with it, not a big deal usually but a fudge not a fix IMHO

      1. petef

        Wot, you mean it's not interpreted as octal for 83?

  11. AM323

    MS is for the hard disk era

    Microsoft 365 Excel: A pack of errors with half of the view full of unuseful command icons, that makes you spend more time solving Microsoft problems than your own work. Everything behaves as if its intuitive source was based on a case of particular illogical and bureaucratic process. Error messages blame lack of user common sense, falsely blames the internet connection, or some technical gibberish that not even Microsoft cares. Google Sheets far better, faster, simpler and honest.

    1. Anonymous Coward
      Anonymous Coward

      Re: Google Sheets far better, faster, simpler and honest

      Nothing, repeat NOTHING from Google can be described as 'honest'.

      Doesn't everything of theirs steal your data and send it to the mothership to be used against you at a later date in the form of Ads?

      Then there is the little issue of sending your spreadsheet data to somewhere that only Google knows where for processing. If your company has rules about data going off-site then you have just broken them. Mind you MS wants you to do the same with Orifice but at least there, they still offer a non-cloudy version that even works offline.

      1. Kane
        Joke

        Re: Google Sheets far better, faster, simpler and honest

        "Doesn't everything of theirs steal your data and send it to the mothership to be used against you at a later date in the form of Ads?"

        Not like Microsoft, eh? Eh?

  12. David-M

    Template template template...

    Never been a problem, and I still use 2003 (no ribbons). What you've always needed to do is change your template to be how you want.

    For example have in the template a tab called "Auto" (which converts) and a tab called "Text" (which is text only) so whenever you create a new spreadsheet you choose the tab appropriate for pasting/typing into. Or do it ad-hoc with Ctrl-1 Text on the columns/sheet.

    Making this as easy to set up as possible is a good thing and I'm sure the new improvements will help that.

    LibreCalc has improved over the years and is good at imports but LibreOffice has never been as nimble as Office with even small files sucking the life out of your computer.

    d

  13. CowHorseFrog Silver badge

    Its amazing nobody has yet spotted the real cause of the problem... heres a clue its not the fact that XL parses incorrectly.

  14. Anonymous Coward
    Anonymous Coward

    Great. We're getting there

    Now we just need the US to use the proper date format - you know, the one every other country on earth uses - and it will be paradise.

    1. Anonymous IV

      Re: Great. We're getting there

      > Now we just need the US to use the proper date format - you know, the one every other country on earth uses - and it will be paradise.

      I appreciate your sentiment - but not your accuracy. "Every other country" does not use dd/mm/yyyy, although many do.

      1. sebacoustic

        Re: Great. We're getting there

        Arguably dd/mm/yyyy is just almost as boneheaded as mm/dd/yyyy. My colleagues in Taiwan use yyyy-mm-dd regularly, that makes good sense as it sorts correctly, Excel date conversion or not.

        1. upsidedowncreature

          Re: Great. We're getting there

          ISO 8601 FTW!

          1. Michael Wojcik Silver badge

            Re: Great. We're getting there

            Yes. Anything other than ISO 8601 is idiotic.

      2. Alumoi Silver badge
        Joke

        Re: Great. We're getting there

        Right, some countries use the correct yyyy-mm-dd or yyyymmdd.

        1. David 132 Silver badge
          Coat

          Re: Great. We're getting there

          Stardates or GTFO.

        2. petef

          Re: Great. We're getting there

          Denmark apparently. I use ISO 8601 as a matter of course and get some way there with a ~/.i18n containing

          LC_TIME=en_DK.UTF-8

  15. Zippy´s Sausage Factory
    Windows

    A good start.

    Now while they're at it, can they finally get round to fixing the page number issue in Word that's been there since Word 2.0c and still isn't fixed? (You know, the one where you print from the preview and every page number is zero*?)

    * Under the right conditions, obviously.

  16. Anonymous Coward
    Anonymous Coward

    While we're bashing Excel...

    Optimist: The glass is ½ full.

    Pessimist: The glass is ½ empty.

    Excel: The glass is January 2nd.

    1. BoldMan
      Joke

      Re: While we're bashing Excel...

      Engineer: The glass is the wrong size.

      1. Ken Moorhouse Silver badge

        Re: Engineer: The glass is the wrong size.

        That reminds me of...

        https://medium.com/jumpstart-your-dream-life/empty-your-cup-a-zen-proverb-on-opening-yourself-to-new-ideas-10e8c9545c7b

        On this particular day, a scholar came to visit the master for advice. “I have come to ask you to teach me about Zen,” the scholar said.

        Soon, it became obvious that the scholar was full of his own opinions and knowledge. He interrupted the master repeatedly with his own stories and failed to listen to what the master had to say. The master calmly suggested that they should have tea.

        So the master poured his guest a cup. The cup was filled, yet he kept pouring until the cup overflowed onto the table, onto the floor, and finally onto the scholar’s robes. The scholar cried “Stop! The cup is full already. Can’t you see?”

        “Exactly,” the Zen master replied with a smile. “You are like this cup — so full of ideas that nothing more will fit in. Come back to me with an empty cup.”

  17. TSM

    The thing that's most annoying about, particularly, the date conversion in Excel is that it doesn't consider any context: "The other 1000 values in this column are text strings, but *this* one is clearly supposed to be a date!" Or "Only 40% of the cells in this column are valid {EU|US} dates; they are all valid {US|EU} dates. But I'm going to interpret it as {EU|US} dates anyway and the ones that don't work can be text."

    The whole idea of spreadsheets is that you have tabular data, i.e. blocks of data where, generally, each column holds the same information for every row. But this concept has never been applied to the parsing logic.

    It doesn't look like the update has done anything about this. It's just allowed us to tell it to stop trying - which is something, at least, but a global solution for a very localised problem.

POST COMMENT House rules

Not a member of The Register? Create a new account here.

  • Enter your comment

  • Add an icon

Anonymous cowards cannot choose their icon

Other stories you might like