• (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)
    Comment held for moderation.

Leave a comment on “Dating Backwards”

Log In or post as a guest

Replying to comment #698460:

Log Out

« Return to Article