• (nodebb)

    PERMIER!

  • (nodebb)

    There's just a problem. Imagine February 1st, 2009- an actual date in the document. We convert the year into 90, the month into 20, rendering the date as 90210.

    Somebody needs to retake basic math, and it isn't me. Or maybe that somebody needs to re-read what was written more carefully. Concatenating the textual representations of 90, 20, and 1 will not produce anything with the 2 followed immediately by a 1.

    I typed the original expression carefully into my local copy of Excel (in UK English):

    =CONCAT(IF(MONTH(A1)>9,YEAR(A1)-2000,(YEAR(A1)-2000)*10),IF(DAY(A1)>9,MONTH(A1),MONTH(A1)*10),DAY(A1))

    And if cell A1 contains 1 February 2009, the calculated result is, indeed, 90201.

    So, once again, the DailyWTF writer is TRWTF.

  • (nodebb)

    And a formula that produces the expected result:

    =TEXT(A1,"YYMMDD")+0

    With the +0 in place to defeat Excel's tendency to add a leading zero on the result of the TEXT function (which returns a string and not a number).

  • RLB (unregistered)

    Now, the first thing: Excel function names are locale specific.

    And this is TRWTF right there. Not just in this case, but everywhere Excel is used.

  • Birion (unregistered)

    gnitad?

  • some guy (unregistered) in reply to Steve_The_Cynic

    Excel function names are locale specific. Yes, and the argument separators as well, which are typically ';' or ','. The former if ',' is already the decimal separator. And NOONE EVER LOCALIZES THE TOOLTIPS IN THIS REGARD!

    Re: 90201 and 90210. The explanation is trivial, Remy was thinking of his favorite Beverly Hills-themed series.

  • erffrfez (unregistered) in reply to RLB

    why is being able to use the actual names for things in the place the program is being used a WTF?

  • erffrfez (unregistered) in reply to Steve_The_Cynic

    maybe Feb 10 that year somehow becomes 90201, I don't know.... because of the title of the post perhaps

  • 516052 (unregistered) in reply to erffrfez

    Do I seriously need to explain why having a programming language whose keywords change depending on your windows settings is a bad thing?

  • (nodebb)

    You need a real ~expect~ expert to do something this bad.

    Last week my boss subjected himself to a webinar (from an expert) on AI prompt engeneering and the key point was that you had to start every new chat telling the agent "you're an expert on [XXXXXX]" to focus it. Now I'm wondering if it really is such agood idea. 😅

  • (nodebb) in reply to 516052

    Do I seriously need to explain why having a programming language whose keywords change depending on your windows settings is a bad thing?

    You'd need to start by explaining why you think that basic Excel formulas are an actual programming language.

  • (nodebb)

    Is today opposite day?

  • (nodebb)

    treating dates as text is bad

    Oh my goodness. I cannot help but admire the sheer luxury of professionalism implied by that statement.

    My job for the last six years has involved parsing the worst possible garbage you can imagine into dates. I've had to do things that not even a priest in a confessional could stomach, but I gritted my teeth and committed them into source control. "Treating dates as text" is but a casual stroll down a sunny park lane in comparison.

  • Jonathan (unregistered)

    What confuses me (familiar with Excel, definitely not an expert) is that if the cell in question is recognized by Excel as actually being a Date, can't you just change the formatting on the cell to YYMMDD?

    Okay, maybe if you need to create some serial number that contains the date in YYMMDD format as part of it you might do this, but, as written, it look like it is being assigned to some cell (the formula starts with a "+"), so that's unlikely to be the case here.

  • (nodebb) in reply to Jonathan

    @Jonathan Excel does "recognise this thing as a date" only to set the default formatting of the cell as a date-ish format, normally one that mirrors the format that Excel found (so if you type 3-Feb-2017, the format will be D-MMM-YYYY). The value in the cell is just a number. (Fractional serial number of the date in question, with the fraction part representing the time of day.)

  • Them Docs (unregistered)

    TRWTF is trying to find out all the different format options that TEXT() supports - helpfully they're not listed on the official page, nor is there a link. They don't support intermingling text (for instance, the letters, 'M', 'D', and 'Y') with your format, unlike the printf/scanf approach, and the 'M' character means either minutes or month, depending on if you use it as 'MM:SS' or just by itself/as part of a recognized date format. This leads me to believe that another WTF is trying to read the code that parses the format text...

  • (nodebb) in reply to Them Docs

    They don't support intermingling text (for instance, the letters, 'M', 'D', and 'Y') with your format

    They do, but you'll probably need to escape things with backslashes, as suggested here:

    https://www.ionos.com/digitalguide/online-marketing/online-sales/excel-text-function/#content-how-to-add-text-to-numbers

  • gman003 (unregistered)

    At one point in college, I was writing a game for funsies, and was using CSV to store all the info on enemies and stuff. For complicated reasons, I was using a composite PK for stuff like equipment (think Faction + WeaponName). And so when enemy definitions referenced this, they needed multiple columns, which I decided to pack into one by delimiting them with semicolons.

    Getting the game to parse that was easy, but since I was using Excel as basically a game editor, I somehow managed to write a function that would split that apart, find the matching row, and spit out the resulting game stats the enemy would have (conveniently telling me if I messed one up), letting me do all sorts of nice stats and summaries to try to balance things. Which I suppose isn't super WTF, but it is the sort of Excel macro you only get out of the hands of someone who knows C.

    I eventually threw out the CSV files and just used a SQLite database, after performance started being an issue. The CSV parser worked but doing an iterative scan any time it was queried is not great - O(N) is a hell of a lot worse than O(log N). I then went on to get a job and not finish the game.

  • Them Docs (unregistered) in reply to Steve_The_Cynic

    Good find! I guess that's not to be confused with the somewhat orthogonal ability to format numerical columns so that they are displayed with a formatted text, the formatting mechanism of which is different again, although they do mention using a backslash (albeit to escape numbers instead of text, which is what it perhaps actually should say, as that is what seems to work). https://support.microsoft.com/en-us/office/combine-text-and-numbers-a32c8e0e-90a2-435b-8635-5dd2209044ad

  • (nodebb) in reply to Steve_The_Cynic

    Like you, Steve, when I need some official documentation for Microsoft software products which have existed for over thirty years, the first place I turn to is the online-sales website of some random "digital transformation" business.

    (For those who have not had their first cup of coffee yet, that was not a dig at Steve.)

  • Offizier Johannes Woodhead (unregistered) in reply to 516052

    #einfüg <stdbib.k>

    #einfüg <stdea.k>

    ganzezahl haupt(leer) {

    druckf("Hallo Welt!\w");

    gibzurück 0;

    }

    /* this is absolutely okay */

  • Randal L. Schwartz (github) in reply to Steve_The_Cynic

    You'd need to start by explaining why you think that basic Excel formulas are an actual programming language.

    Hey, if it has decision and repetition, it's probably Turing Complete and passes as a language. As long as you can code Towers of Hanoi, or something like that.

  • Poor Date Formatter (unregistered)

    ANNEE are you ok, are you ok, are you ok ANNEE? You’ve been hit by, you been struck by, a poor date formatter

  • (nodebb) in reply to dpm

    Yeah, I was disappointed by that, too.

  • (nodebb) in reply to Randal L. Schwartz

    if it has decision and repetition

    Decision, yes, that's sure. (example formula with decision: see my first post on this sorry tale)

    Repetition, not so sure, outside of the implicit repetition in e.g. "array formula" operations.

  • (nodebb) in reply to dpm

    Like you, Steve, when I need some official documentation for Microsoft software products which have existed for over thirty years

    I squinted for a moment when I relooked at this, until I realised that the first time I used Excel was in the early part of 1989, which is, indeed "over thirty years" ago.

    So, OK, yes, I'll let you off this time.

  • (nodebb)

    Lotus Notes, despite all its many (MANY!) quirks was very forgiving when entering dates, especially on the web. You could enter Feb 23, 2009, 23 Feb 2009, 2/23/2009, 23/2/2009 and several other variations and they'd all get correctly stored as The 23rd day of February in 2009 in an actual date field. It was almost useful. Why "almost?" Because if there was ambiguity, like 2/9/2009, the result could be Feb 9 or Sept 2 depending on assumptions made somewhere in the labyrinth of configurations and there wasn't any way to tell whether what was stored was what the user intended.

  • 516052 (unregistered) in reply to Steve_The_Cynic

    Because they are. There is nothing basic about excel and there newer was. The thing is a fully fledged IDE with a builtin language for accounting and mathematical operations and charting. And with VB it's capable of just about anything. So treating it as a glorified calculator is a very bad mistake.

  • RLB (unregistered) in reply to erffrfez

    why is being able to use the actual names for things in the place the program is being used a WTF?

    Because opening a customer's Excel sheet and finding nothing works because they use a Dutch (or, on one occasion, Frencj) version of Excel and I use an English one is not helpful at all.

  • RLB (unregistered) in reply to Steve_The_Cynic

    Excel does "recognise this thing as a date" only to set the default formatting of the cell as a date-ish format,

    Cue joke about the glass being the 1st of february.

  • petz (unregistered)

    Don't get me started on localized Excel functions. In my country you can't be sure if Office is set to English, French or German. Most Excel functions are translated but some more obscure ones are not. Except after the next major office update when it's suddenly translated (or not anymore) but not automatically recognized so you get tons of errors because your excel file you've been using for literally decades suddenly uses 'unknown' functions.

  • A guy I knew (unregistered) in reply to 516052

    ...because when you are a non-english speaker but just trying to get your job done, it is actually SUPER helpful when you stand a vague chance of being able to accomplish what you set out to achieve.

  • 516052 (unregistered) in reply to A guy I knew

    That sounds good in theory but in reality things just don't pan out that way because human learning does not work that way.

    People, even us professionals, do not learn software by thinking. We learn software by memorizing patterns and relationships.

    Take for example the save icon. When you need to save something you don't stop to think which of the many icons could possibly be save, look at the floppy icon, recognize it as a visual representation of an obsolete storage medium and than draw the conclusion that storage = save. You just look for the little blue square icon because that's what you memorized.

    And the same is true for function names in any program. People do not treat these as actual terms to understand and parse. They just memorize what magic word does what.

    Thus in practical terms having the names localized really gives you very little to no benefit. What does benefit you is some sort of worldwide massive repository of all knowledge that can tell you what magic word to use.

    And localizing function names thus becomes a determent because it fragments your possible base of answers.

    Sure, you might be able to find an answer in German or French as to what the equivalent of Countif does. But will you find it in maleness? Belorussian? Khmer? And what if you need to find an answer to something more specific like how to perform some complex operation? If you are lucky there might be one reply on stackoverflow that is in english and only english.

    So functionally from every perspective it is better for operators to learn the magic words in the magic language of the program than to localize everything.

  • (nodebb) in reply to 516052

    Because they are. There is nothing basic about excel and there newer was. The thing is a fully fledged IDE with a builtin language for accounting and mathematical operations and charting. And with VB it's capable of just about anything. So treating it as a glorified calculator is a very bad mistake.

    Excel is a tool that is not targeted at professional programmers. It is targeted at ordinary office workers who frequently just want to do some basic calculations. Of course they want the function that extracts the month from a date to be in their own language.

    Furthermore, I don't know if you have tried it, but, if you change the language settings on your computer and then open an Excel spreadsheet, the built in function names are automatically translated. For example, if I create a spreadsheet with the formula =MONTH(A1) in A2, and then change the primary language on my Mac to French, after reopening the spreadsheet, it will say =MOis(A1).

    And the same is true for function names in any program. People do not treat these as actual terms to understand and parse. They just memorize what magic word does what.

    This is nonsense. Normal people using Excel looking for a function to average a list of numbers will look for a function called "average" in English. They'll probably look for "moyenne". similarly, the menu option to save your document seems to be "Enregistrer" not save (under the Fichier menu - interestingly, the shortcut is still ⌘s).

    Don't forget, we are not talking about a programming language used by professional programmers here, we are talking about a tool used by millions of ordinary people who would rather the magic words are in their own language.

  • 516052 (unregistered) in reply to jeremypnet

    This is nonsense. Normal people using Excel looking for a function to average a list of numbers will look for a function called "average" in English. They'll probably look for "moyenne". similarly, the menu option to save your document seems to be "Enregistrer" not save (under the Fichier menu - interestingly, the shortcut is still ⌘s).

    They are not going to look for anything. They are not even going to think. They are going to google "how to average numbers" in their own language and brainlessly copy paste what ever the first response is.

  • Daemon (unregistered)

    All developers know: Dates are a hard problem. Sécond dates are impossible / unheard of. ;-)

    That said: The only valid format is YYYYMMDD. It's the only sortable format. ( / Only format that can be sorted in a meaningful sense. )

  • (nodebb) in reply to 516052

    They are not going to look for anything. They are not even going to think.

    You may want to stop being snobbish and start living in the real world, down here with the muggles.

    First time I used Excel was in high school as a summer job, writing macros for the accounting department of a friend's company. I was glad everything in Excel was in French as I knew little what back then. That was in the 80s and there was no Internet and no Google to use. Everything I did, I had to come up with it by myself by discovering it on the fly from the menus or the paper documentation. I had never used Excel before and yet I delivered what they wanted. I'm sure my macros were terrible but they got the job done and I regretted nothing.

Leave a comment on “Dating Backwards”

Log In or post as a guest

Replying to comment #698483:

Log Out

« Return to Article