- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Wow, that's messed up. My only problem with Excel is the limit in the number of rows of data you can have.
Admin
What? 1 million rows isn't enough?
Remember, Excel is not a database.
Admin
Admin
[quote user="Fowl"][quote]Remember, Excel is not a database.[/quote]
Sure, you know that, I know that, but tell that to the management...
Well the company i worked for didnt exactly use excel as database, but as a reporting tool. And for formatting data to be entered to the database. and... you get the picture.
Captcha: vulputate... hmmm
Admin
TRWTF is that excel is smart enough to convert 1 to Jan, but not smart enough to realize that DAYS_IN_JANUARY < 48.
Admin
The cry frequently heard around our house, usually directed at a popular software package - "STOP HELPING ME!!"
Admin
As long as your middle name is not shortened as M. you're lucky.
Admin
I've been dealing with Excel's fucked up data since the dBase days. Excel is Satan's own tool.
Admin
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
Admin
that would be January '48 or January of 1948
Admin
So, what, it's too much trouble to type "Unit 1, Apt. 48"?
Admin
We have this web application that shows a table of information about a large group of people. There's a feature whereby the user can export the data for all the people they're viewing at the moment (after, e.g., filtering the set of people somehow) to an Excel file.
The people who use this insist on this bizarre format for the Excel file that involves all of the field names in the first column, and all of the data in the second column, with each person's data appended below the one before (including a repeat of all the field names). Since there are around 70 fields for each person, the Excel file contains about 70n rows for n people. We are having quite a time explaining to them that the problems that occur when n gets up around 1000 are not our fault.
Admin
I KNOW!!
I really hate software that "helps" me. When I type something into Excel, what I type is what I mean.
And sometimes when converting a cell to a plain text, Excel will not listen and still format the data. This was 1 or 2 version of Excel ago though so maybe that's fixed by now but boy, that irritated me. "No really, I'm sure you do want this to be automatically formatted to be a date..."
Arg.
Admin
"TRWTF is that excel is smart enough to convert 1 to Jan, but not smart enough to realize that DAYS_IN_JANUARY < 48."
you think maybe it means the year! as in January 2048
Admin
And now we have TRWTF.
Admin
Of course, the real WTF is that you have to format the cells as text before you enter the data, otherwise you end up with (in this case) 17533, which the postman would be even less likely to understand.
Of course, if you are importing from CSV or something similar (which I hope they are - storing the order information in a database and just using excel to print shipping labels/invoices), you don't get the opportunity to fix the formatting.
And while we are talking about Excel, whyTF doesn't it let me format data as a time span? Why does it assume that any data that looks like "10:32" must be a time-of-day? Since it correctly handles time subtraction, it must have the concept of a time span in there somewhere, but I haven't found a way to enter one.
Admin
Damn .. I just found out that Open Office Calc 2.4 has a huge bug. When you put 1/48 into a cell and format it as a date, it remains as 1/48.
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.
Stupid program should be exactly the same as Excel.
Admin
Interestingly enough, Excel 2003 does not do this for me - if I type 1/48, it stays as 1/48. Same with 1/32. If I type 1/31, though, it becomes 31-Jan.
Admin
It looks like you're writing a complaint! Would you like help?
Admin
"Excel injection" hacks. Cool.
I wonder what company that is? ;)
Admin
In fairness to Excel, it's not translating it to the 48th of January, but rather, January '48. Month/year, not month/day.
Admin
It could be worse, and give you "0.0208333333". :-)
Admin
Oh, I think this could get pretty interesting, actually. Maybe he should enter his address as "=INDIRECT(ADDRESS(ROW(),COLUMN()+1))" and see what comes back.
Admin
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.
Admin
Yeah! I say there are 48 days in January, and no damn open-source hippie is going to tell me otherwise!
Admin
That's happened to my girlfriend too with her payslips from the job agency she works for.
She noticed her address on her payslips were sent to to 02:00:00 (her address being 2A). I immediately knew what was wrong; something assumed it was the time, 2AM.
The week after the first time she phoned the agency to change it, it was still going to 02:00:00. She phoned again, and they said that when they typed in 2A and re-opened whatever system he had, it turned back in to 02:00:00.
Now they specially write her payslips by hand before sending them off.
Admin
Isn't the raily WTF that the company wants him (the customer) to type-in an excel-compliant address instead of handling this "problem" internally? ;-)
Admin
Yeah, another example of an MS app "helping" you. I really, really, really hate that too and all MS apps perform that "intelligent highlighting"
Intelligent my ass.
Admin
The real problem is that it stores the data as a time of day -- or rather that it only allow you to edit it as a TOD once it has made up its mind that you have entered a date.
This is the problem that the genetic researchers are facing, and you can see the problem if you enter "55:00" (without the quotes) in a pristine cell in Excel. This is then formatted as "55:00:00". Ok, you think. I just want to get rid of the seconds, so you press Ctrl-1, select Custom format and enter "hh:mm". Excel then displays your value as "07:00". "WTF!" you exclaim.
The reason for this is of course that "55:00" is two days and 7 hours. "hh:mm" is intepreted by Excel as "show the hours and minutes portions".
The solution is to specify the format "[h]:mm" instead.
You still have a problem, though, in that you cannot edit the value as a duration. In edit mode, the value is shown in as "02/01/1900 07:00:00" (in the UK -- in the US I suspect it is shown as "01/02/1900 07:00:00".
<sigh>Admin
What's funny about the highlighting all the text thing is that i've NEVER seen a user actually want that to happen - ever!
Watching your grandma and other "normal" people struggle against software that "helps" you: priceless.
And there is no magic way of turning it off. Grr.
Admin
Annoying as it is, there is a standard way to deal with that. Click where you want the selection to start, and hold down the button. Sweep left, past the beginning of the word, then back right to select the rest of the text. Notice the selection rectangle grabs the beginning of the word when you move left, but drops back to your desired point when you go back right. Now continue to drag right past the end of the last word, and then reverse, and let go when you have the mouse where you want the selection to stop. Word/IE/whatever will do the same thing: select the end of the word as you sweep right, and do a letter-by-letter selection as you go back left.
You can get both "intelligent" and non-intelligent selection in the same operation that way.
Admin
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:
The way it butchers postal codes that start with a leading zero, like the town I grew up in (Granby, MA 01033 USA)
Dates of any kind
Serial numbers that have leading 0's (see #1)
The JET database driver for Excel. One large WTF.
SQL Server Integration Services Excel datasource. WTF squared.
The f-ing "just put an apostrophe" workaround. WTF.
a. The equally effective "format as text before you paste" workaround. Gives the illusion of working, only to break later.
Save as CSV, then reopen the CSV in Excel. Lots of magical things happen there.
While on the topic, CSV files, which are a whole WTF on their own.
The Jet database driver's "type guess rows" registry entry. WTF factorial.
The root of all this: Excel makes things that look like tables, and tables are useful for data. There is no other program that is as widespread AND makes things that look like tables, so people use Excel to make tables of data. And it's in fact really, really bad at that. It was designed for ad-hoc numerical analysis and got appropriated as a database loading and reporting tool.
I think it's actually damaged the GNP of whole nations, this Excel program. It'd be interesting to know how badly.
Admin
Regarding the "oh just format the cell as text": If you format the cell as text and then type 1/48 into it, Excel leaves it as 1/48, but still bitches that "This cell contains a date string represented with only two digits for the year."
Admin
Admin
Ah, thanks that does work but how about we just don't have it try to guess what I want to highlight in the first place?
In Firefox it grabs the selection and nothing more and that is really what I want.
I also hate how double clicking on a word (to select just the word itself) also gives you the space after the word. That is NOT what I want.
Thanks for the tip though, I'll have to remember that.
Admin
Microsoft Word -> Tools -> Options -> Edit. Uncheck "When selecting, automatically select entire word".
Admin
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.
Admin
Is this a cultural thing? I've never heard of formatting an address like that, so I'm not surprised that the (presumably also American) devs for Excel had never heard of it either. We might use "1 Maple Street #48" or "1 Maple Street Unit 48" or "Apt. 48, 1 Maple St." or some variation on that theme, but the forward-slash notation is totally new to me.
Admin
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.
Admin
Two more things I hate about Excel, which still aren't fixed even in Office 2007:
if you have a file called Foo.xls open in one directory, and you attempt to open Foo.xls from another directory at the same time, Excel yells at you. Why can't they include the directory structure as part of the file name? Other Office apps do not have this problem.
Funky clipboard handling. Clipboard seems to empty itself after subsequent actions, even if the copy or cut was done in another application.
And don't get me started on that effin' ribbon in Office 2007 and them taking away my ability to make my own buttons and customize my menus and toolbars.
Admin
Merlin, bingo - my centiments exactly, this is nothing to do with the user... how can they say "You can avoid this by entering your street address on one line: 1/48 YourStreetHere St." it's just not the punter's problem!
Admin
I deal with CUSIP numbers a lot in my job. Sometimes I have to load a CSV of data into Excel for various purposes.
A CUSIP is a number that identifies a stock offering (in the US). It is nine alphanumeric characters (mostly digits, but sometimes one to three of the characters can be alpha).
A typical one might be 87654E210. Upon loading, Excel translates this into "8.765E214". USELESS.
Admin
Almost made me puke over my slightly-late report I was working on. Did you know that if you format an Excel cell in "percent" format, Excel helpfully decides that typing an integer , say 37 means you meant to enter 37% ?
Entering 0.37 magically turns into 37% too.
Insta-data-crash
Admin
Yes, other countries have different notations for addresses. When I lived in Germany, my street address was "Obentrautstr. 32 -- 3", which meant building 32, unit 3 on Obentrau Street. It struck me as quite unweildy.
But here's the thing. If you've never travelled overseas, there's no reason why you should know about address notations in other countries. But if you're writing a program that will be part of the most popular office suite in the world, and will be used by people in virtually every country on earth, you really ought to be up on this sort of thing.
Admin
I can top all of your I hate Excel stories at my last job my boss would do all the designs for our HTML projects in Excel. Every page single page. The best part was when he tired to make a non-trivial change to the design he basically had to redo the entire design. Never have I seen a tool so misused before.
Admin
I bet that they bought an off the shelf label printer to print the mailing addresses, and the best way to interface to the label software is through Excel.
The only two cases I have ever seen a "/" in an address would be if it starts with "C/O" for Care of, or if it is "123 1/2 Main St". I'm not sure why the company should alter their process for a 1 in a million address that fails because it is just "1/48" on a line.
Admin
Folks,
There is a simple way to dumbify Excel.
Open a new worksheet, click in the upper left corner, to select the whole worksheet, then right click, format cells, "Text". Then save it as a default template.
Admin
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.
Admin
That only barely helps. Excel still converts the data to an underlying format. Formatting the cells as text just changes the way they are displayed.
Admin
Haven't been using ms apps recently, but last time I did, dragging left before dragging right solved that problem. Well, minimized it, really.
Addendum (2008-04-04 12:44): Just saw someone answered before me, but WTH.