• ChiefCrazyTalk (unregistered) in reply to dave
    dave:
    TRWTF is that excel is smart enough to convert 1 to Jan, but not smart enough to realize that DAYS_IN_JANUARY < 48.

    Ummm no. Excel converts 1/48 to Jan 48, true, but if you look at the actual data it is January 1, 1948 not January 48th.

  • (cs) in reply to maldrich
    maldrich:
    Anyone who has worked with a database in a professional capacity for more than 20 minutes should have a list of at least 10 reasons why Excel is a monster. These probably include:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

  • sweavo (unregistered) in reply to dave

    and dave's not smart enough to realise NUMBER_OF_YEARS_IN_CENTURY > 48

    Ban all "smart" features now! They reward user-dumbness and penalise users who know what they are typing.

    Open Office is worse. If you type a date in the word processor and hit return, it decides you must have wanted a numbered list. WTF?!

  • Jay (unregistered)

    I ran into this same problem with a system that tried to transfer data from our database to Excel via CSV files. Our data included part numbers that consisted of letters, digits, and hyphens. On one of our first tests I saw a bunch of valid-looking part numbers, and stuck in the middle was "12-Jan-2004". It was a while before I figured out that the part number was originally 1-12-2004.

    But ultimately we figured out a simple trick: Any field that could be interpreted as a date, write out with a leading space. Thus we wrote " 1-12-2004", and Excel imported it correctly. When we accepted data back from Excel, we had to strip off the leading space.

    I'm sure there are applications where this would be unacceptable, depending on what manipulations you needed to do on the Excel side, but it worked for us. As there was a space in front of ALL part numbers, they still sorted correctly and so on.

  • Ross Presser (unregistered)

    At the end of 2000 my accounting users came to me with a problem. They had been used to typing "1/98" for January 1998, "5/99" for May 1999, "7/00" for July 2000, etc. But when they typed "3/01" for March 2001, they instead got March 1, 2000.

    I explained that they'd been depending on a quirk of Excel's behavior that I couldn't change, and that there was no way to configure Excel to know that "3/01" always meant March 2001 on a new blank spreadsheet. They got very irate with me and tried to get the boss to force me to do it anyway.

    CATCHPA: veniam. What the hell is that, some kind of digital deer meat?

  • Ross Presser (unregistered) in reply to maldrich
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    This isn't limited to Excel. A surprising number of web programmers think that all zip codes can be stored in an integer, converted back to a string, and still be five digits. Their web app then yells at me that 8030 is not a valid zip code.

    Offenders have included Yahoo and Homestarrunner.com, in the past.

  • Jay (unregistered) in reply to enough!
    enough!:
    My big problem is when ms apps automatically "correct" selected text. For instance if I wanted to select "em ipsum dol" in the text "lorem ipsum dolor" in ie, it automatically grabs the full phrase. I wish it would just respect where I started dragging to the end.

    My favorite was when I was writing some software documentation, and I wrote "Enter your drive letter followed by a colon (for example, C:)". And MS Word changed the ":)" to a smiley face.

    Less amusing but more annoying: When I am trying to type program code into a document and the software insists on capitalizing the first letter of each "sentence". No, it is not "For (double f=1.0;F<2.0;f+=0.1)". The compiler will not accept that.

    And I have fond memories of the time my daughter tried to use Word to type up her math homework. She wanted to put "7", next line "+3", next line underscores, next line "10". She typed "7" and pressed enter. Word promptly displayed "8". That was a new "feature" at the time and it took me quite a while to figure out how to turn it off.

    Microsoft seems to have this bizarre idea that if their software is too complicated to use and people can't figure out how to get it to do what they want, the solution is to have it guess what they want and do it for them.

  • lw42 (unregistered) in reply to Jon B
    Jon B:
    maldrich:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    This drives me crazy in general. ZIP CODES ARE STRINGS NOT INTS! I especially love it when an app takes your STRING entry, parses it to an INT (bye bye leading zero), then turns it back into a STRING and checks the length. Thank you for invalidating my input before validating it.

    Jeez, guys, just how hard is it to move to an area with a truly five digit zip code?

    Some people will whinge about anything.

  • (cs)

    The person living at 1-48 must be in Canada or at least not in the US. I don't know why s/he (Spikeles?) decided to put the unit and street number on a separate line though.

    Japanese addresses are the most challenging as there's soooo much data. Often, they can shorten it though, thankfully.

  • cdeh (unregistered)

    I always enjoyed this nice feature: http://www.metacafe.com/watch/838403/why_i_love_excel/

    WTF?

  • Migala (unregistered) in reply to maldrich
    maldrich:

    I think it's actually damaged the GNP of whole nations, this Excel program. It'd be interesting to know how badly.

    I have strong suspicions the current sub prime mortgage crisis has to do with the widespread use of Excel for (risk) analysis in banks.

  • Saaid (unregistered) in reply to Ross Presser
    Ross Presser:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    This isn't limited to Excel. A surprising number of web programmers think that all zip codes can be stored in an integer, converted back to a string, and still be five digits. Their web app then yells at me that 8030 is not a valid zip code.

    Offenders have included Yahoo and Homestarrunner.com, in the past.

    I don't know about homestarrunner, but I heard that Yahoo uses Excel as the back end database.

  • (cs)

    You know...

    Storing US postal codes as integers is not such a crime, so long as your postal code type has an appropriate ToString() method defined that takes care of padding as well as an implicit operator to turn strings into ints (easier than writing zip.Parse all over the place).

    Alternatively, you can make that abstraction at the database level, if you really can't spare the extra couple of bytes per zip... Just make the database present it as a padded string but store it internally as an int.

    Once again, however, they must be US postal codes if you want to be able to make that assumption. For canadians or all sorts of other countries, you get vastly different postal codes which can't be represented by a numeric type efficiently.

  • Robert (unregistered) in reply to Simon

    Even German Excel does this nonsense. Nobody in Germany writes dates with a slash. German uses dots.

    Aren't you all glad Microsoft forced and cheated their document format through the ISO standardization process?

  • Alcari (unregistered)

    Ahh, so familiar. I live in apartment 303, house 394.

    Up to a few months ago, I used the fill this in, as in common here, as 303-394. Until that is, I recieved an email stating that my package could not be delivered, because "apartment number -91.0" does not exist.

    Now, I could sort of understand this, except that 303-394 != -91

  • (cs)

    you cannot always assume that Excel will format a cell correctly, even if you specify the format as text. If there are more than a certain number of characters, something like 64, (I cannot recall exactly and REALLY do not feel like investigating), and the format is set to "text", then it only displays pound signs. Changing the cell wrapping and size will not fix it. You have to change the formatting to "general". But, if you set that column to "general", and insert text that is too short, then a plethora of other formatting issue occur; depending on what is in the field, it'll convert to a number or a date, etc.

  • (cs) in reply to Someone You Know
    Someone You Know:
    maldrich:
    Anyone who has worked with a database in a professional capacity for more than 20 minutes should have a list of at least 10 reasons why Excel is a monster. These probably include:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

    Hahahahaha I can just imagine the parent newsletter...

    "Schools 1.3E+11, 1.3E+11, 1.3E+11 and 1.3E+11 will be closed for staff conferences, next Friday."

  • ExcelPowerUser (unregistered) in reply to Simon
    Here I was, all ready to say that Excel wouldn't do something *that* dumb... then, lo and behold, I fire up Excel 2007, type in 1/48... and I get Jan-48.

    If that isn't a bug, I don't know what is. :P

    Not really. It's assuming you've typed in a date in the form of mm/yy. Come 2048, you won't think twice about seeing Jan-48 for 1/48.

    I think the best response, though, is that person above who said "STOP HELPING ME!" That's going to be my new Microsoft mantra....

  • (cs) in reply to Alcari
    Alcari:
    Ahh, so familiar. I live in apartment 303, house 394.

    Up to a few months ago, I used the fill this in, as in common here, as 303-394. Until that is, I recieved an email stating that my package could not be delivered, because "apartment number -91.0" does not exist.

    Now, I could sort of understand this, except that 303-394 != -91

    This is the real WTF. Try your math again. I think your ALU has a bug.

  • Alcari (unregistered) in reply to Alcari
    Alcari:
    Ahh, so familiar. I live in apartment 303, house 394.

    (...)

    Now, I could sort of understand this, except that 303-394 != -91

    Then again, I might be the idiot, for not recalling my correct adress in one post... 303-396, one door over.
  • (cs) in reply to Alcari
    Alcari:
    Alcari:
    Ahh, so familiar. I live in apartment 303, house 394.

    (...)

    Now, I could sort of understand this, except that 303-394 != -91

    Then again, I might be the idiot, for not recalling my correct adress in one post... 303-396, one door over.

    Hmm... So your ALU is functioning properly. I guess you were suffering from a TLB bug, then, and encountered corrupted data in cache.

    Maybe you made the same mistake back then?

  • (cs) in reply to Someone You Know
    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

    No way! My parents live in Westfield. Small world.

  • (cs) in reply to Opie
    Opie:
    Someone You Know:
    maldrich:
    Anyone who has worked with a database in a professional capacity for more than 20 minutes should have a list of at least 10 reasons why Excel is a monster. These probably include:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

    Hahahahaha I can just imagine the parent newsletter...

    "Schools 1.3E+11, 1.3E+11, 1.3E+11 and 1.3E+11 will be closed for staff conferences, next Friday."

    Yeah. The first six digits are the same across all the buildings in a given school district, so you wind up with long lists of the same scientific notation numbers.

  • <Insert Name Here> (unregistered) in reply to dave
    dave:
    TRWTF is that excel is smart enough to convert 1 to Jan, but not smart enough to realize that DAYS_IN_JANUARY < 48.
    That doesn't appear to be the problem. Excel interprets it as 1/1/1948 and not 1/48/2008. I don't see it as a problem with excel so much as it's a problem with the way it's being used.
  • RandomWTF (unregistered) in reply to n9ds

    So very true.

  • (cs) in reply to maldrich
    maldrich:
    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

    No way! My parents live in Westfield. Small world.

    As do mine. But I got out as quickly as possible. Not that it isn't a nice town, but it can be very boring (should that be "wicked boring"?), that traffic light at the end of the Green Bridge has been broken for about ten years now, and the novelty of being woken up by A-10s screaming over your house at about thirty feet wears off quickly.

  • Josh (unregistered)

    Has anyone else noticed that when doing a Data/Text to Columns and then selecting Delimited, Excel looks to see whether your data is comma or tab delimited and defaults to the opposite? It seems that no matter which one I did last, it ALWAYS picks the opposite of what I'm trying to process at the time.

  • (cs) in reply to JJ

    TRWTF is that "Jan-48" should have been displayed as "Feb-17"

  • al (unregistered) in reply to maldrich
    maldrich:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    Not just numbers with zeros, but any number. Excel absolutely insists on butchering them. If you have a long number, it has to display it in exponential notation, even if you select text. And increasing the number of significant digits? Then excel just gives you more zeroes, 1.23456789000000E+9. W T F.

  • Franz Kafka (unregistered) in reply to talaxor
    talaxor:
    Nope. Not nearly enough.

    First off, excel is not a database, but it's a really easy way for non-tech folds to look at data. And no, Access is not the answer. It's too hard and confusing.

    I work as a deputy auditor for a large county in Ohio.

    We've got 400,000 properties. We have a file of the sales / transfers for each parcel. An average of 6 sales per parcel = 2.4 million lines

    Now, we can split the data based on years, type of sale, type of property etc., but when you need a report that spans those boundaries, it makes you think "Gee, it'd be nice if Excel could just support an infinite number of rows, or let me set how many rows I wanna deal with"

    And of course, over the course of time, the number of sales / transfers/splits/merges/who knows what will continually increase the size of this file.

    Sounds like you need a database.

  • James Schend (unregistered) in reply to dave

    Jan-48 is Excel-speke for "January, 1948". (Or perhaps January, 2049 depending on where the Y2K cutoff is.)

    That's not really a WTF... if you formatted the field in a different way, it should show the full string "January 1, 1948."

  • Pan_theFrog (unregistered) in reply to Jon B

    At least it doesn't try to do math with zipcodes (63043-4576) But give MS time. I am sure they will see a need to automathically mess with everything that looks like a math problem.

  • (cs) in reply to maldrich
    maldrich:
    7. Save as CSV, then reopen the CSV in Excel. Lots of magical things happen there.
    1. While on the topic, CSV files, which are a whole WTF on their own.

    Excel's CSV handling haunts my nightmares. Thank goodness OpenOffice has such wonderful CSV support. OpenOffice can also open fixed field width text files and let you plot the field break points manually... it's great!

  • KG (unregistered)

    It sucks when people try to use Excel as a database, true. But I know a few people who claim to "know" Access, then wind up using it as a spreadsheet. They've never even heard of SQL.

  • (cs) in reply to Jay
    Jay:
    Less amusing but more annoying: When I am trying to type program code into a document and the software insists on capitalizing the first letter of each "sentence". No, it is not "For (double f=1.0;F<2.0;f+=0.1)". The compiler will not accept that.

    TRWTF is anyone using M$ Word to write code!

  • (cs) in reply to Opie
    Opie:
    You know...

    Storing US postal codes as integers is not such a crime ...

    yes, it is. Postal codes are text. You stay away from my systems, you hear me? I'm watching you...

  • (cs) in reply to Someone You Know
    Someone You Know:
    maldrich:
    I've had this problem as well. In addition to having grown up in Westfield (01085), I now work in the New York public education system, wherein school districts and buildings are all identified by strings of twelve digits known as "BEDS Codes". When entered in Excel, these are subject both to having leading zeroes dropped and to being senselessly converted into scientific notation. Ew.

    No way! My parents live in Westfield. Small world.

    As do mine. But I got out as quickly as possible. Not that it isn't a nice town, but it can be very boring (should that be "wicked boring"?), that traffic light at the end of the Green Bridge has been broken for about ten years now, and the novelty of being woken up by A-10s screaming over your house at about thirty feet wears off quickly.

    I hear ya. (Written, not coincidentally, from Seattle, WA)

  • aka (unregistered) in reply to OzPeter
    OzPeter:
    Also 1/31 is treated as a date, but 1/32 is not. Likewise for 2/28 is a date, so is 2/29, but 2/30 is not

    Which only demonstrates the narrow-mindedness and ignorance of OpenOffice's designers. In Sweden, there was a February 30, 1712.

  • James (unregistered) in reply to al
    al:
    maldrich:
    1. The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)

    Not just numbers with zeros, but any number. Excel absolutely insists on butchering them. If you have a long number, it has to display it in exponential notation, even if you select text. And increasing the number of significant digits? Then excel just gives you more zeroes, 1.23456789000000E+9. W T F.

    I'm reminded of the even scarier tale of the small company which stored customer credit card numbers in Excel - as 16 digit numbers, which of course Excel stored as floating point with about 15 digits of precision. This was fine, until they tried to bill customers for the second installment - at which point, that rounding had corrupted the last digit. Oops.

    (Thanks to the checksum, the guy they called for help was actually able to salvage the numbers and get things working properly. Definitely not good from an evolutionary point of view!)

  • Pitabred (unregistered) in reply to talaxor
    talaxor:
    Nope. Not nearly enough.

    First off, excel is not a database, but it's a really easy way for non-tech folds to look at data. And no, Access is not the answer. It's too hard and confusing.

    I work as a deputy auditor for a large county in Ohio.

    We've got 400,000 properties. We have a file of the sales / transfers for each parcel. An average of 6 sales per parcel = 2.4 million lines

    Now, we can split the data based on years, type of sale, type of property etc., but when you need a report that spans those boundaries, it makes you think "Gee, it'd be nice if Excel could just support an infinite number of rows, or let me set how many rows I wanna deal with"

    And of course, over the course of time, the number of sales / transfers/splits/merges/who knows what will continually increase the size of this file.

    Unfortunately, you're dealing with complex data. You're going to need a complex tool to do so... by using Excel, you're just setting yourself up for failure.

    Thanks for wasting future tax money!

  • Pitabred (unregistered) in reply to Pecos Bill
    Pecos Bill:
    Jay:
    Less amusing but more annoying: When I am trying to type program code into a document and the software insists on capitalizing the first letter of each "sentence". No, it is not "For (double f=1.0;F<2.0;f+=0.1)". The compiler will not accept that.

    TRWTF is anyone using M$ Word to write code!

    I can see it if you're creating a document to distribute to students, giving them code examples.

    I really, really doubt that someone who can see the problems in Word is actually trying to code in it ;)

  • LEGO (unregistered) in reply to Anon
    Anon:
    maldrich:
    8. While on the topic, CSV files, which are a whole WTF on their own.

    The thing I love about CSV files in Excel is how in countries that use comma as the decimal separator, Excel produces semicolon separated files instead. This is great fun if you exchange a CSV file between say the US and France.

    Pipes baby! The only way to delimit...

  • Christopher (unregistered) in reply to Zylon
    Zylon:
    Ahem.

    Microsoft Word -> Tools -> Options -> Edit. Uncheck "When selecting, automatically select entire word".

    Ahem.

    That doesn't work in IE, or programs that use IE's engine.

  • Ullam (unregistered) in reply to Jon B
    Jon B:
    This drives me crazy in general. ZIP CODES ARE STRINGS NOT INTS! I especially love it when an app takes your STRING entry, parses it to an INT (bye bye leading zero), then turns it back into a STRING and checks the length. Thank you for invalidating my input before validating it.

    Poor man's way to check tyour zip code is numeric.

  • G Money (unregistered)

    TRWTF is that Spikeless, having observed the result of entering 1/48 for his address, and having an idea of where the problem might reside, tries the same thing again, expecting a different result. Dumbass.

  • (cs)

    Another WTF is that the person doesn't know how to properly enter his own address. A quick visit to usps.com would show him how.

    Yes, I know this is a bigger mistake of Excel and the behavior drives me crazy too.

  • Excel (unregistered) in reply to maldrich
    I think it's actually damaged the GNP of whole nations, this Excel program. It'd be interesting to know how badly.

    According to my calculations, our GNP was off by 65535% last year due to this!

  • tcas (unregistered) in reply to cdeh

    Some compulsory reading for you: http://en.wikipedia.org/wiki/Floating_point_arithmetic#Representable_numbers.2C_conversion_and_rounding

    You expect Excel to do arbitrary-precision arithmetic for you. WTF?

  • Drax (unregistered) in reply to Jon B

    Why don't you follow postal addressing standards?

    BLDG 1 APT 48

  • Saccharissa (unregistered)

    It's not just backslashes, either. I had to deal with a CSV file of car makes and models. I decided to open it in Excel, and discovered to my dismay that my Saab 9-3's and Saab 9-5's had become Saab Sep-03-07's and Saab Sep-03-07's. Arg.

    I also had a run in with Word's AutoCorrect while I was writing my undergrad thesis. I was dealing with two reference frames, the original (axes x, y, and z) and the hatted (axes x hat, y hat and z hat) systems. I finished writing one chapter and printed it out, only to discover that Word had surreptitiously replaced all instances of the word "hatted" with the word "hated." Gave the chapter a whole different feel...

Leave a comment on “Jan-48”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article