Excel <> helpful

I first ran into MS Excel in early 1993. It was amazing. Primitive by today's standards of course, but amazing. Nearly 20 years later I've had to deal with XL nearly every working day. I remain amazed. Other things too. Frustrated, exasperated, annoyed, and other emotions of that ilk. (You do know what an ilk is, don't you?)

Today was a good example. It's the version of XL with the ribbon. No, I don't know if that's a trademark. Let's just assume that all the appropriate words have the appropriate trademark symbol that looks like this ™. I think it's 2010 running on Windows 7. As if that means anything to anybody.

It's also the version of XL that goes over the top trying to be helpful. It makes me crazy. Today was dates. I approached this data migration with trepidation because there was at least one date field involved. I got a dump from one database, with the date in text, in dd-mmm-yy style. (which is really stupid. (this is foreshadowing.)) The requirement to be imported into the other database was yyyy-mm-dd. Which is a custom date format, which I know how to deal with. But first came figuring out how to convince this version of XL that the original was a date in the first place.

Eventually I got there. The last step I discovered that my column of dates went from the mid-50's, to a couple months ago. (No comment.) When I tried the fix I had discovered and tested with a smaller set of data, I found I could only convert yy to 20yy or 19yy. And as soon as I did one of them it messed up the date format so I couldn't work on the rest. I had to play with it a bit. And then I discovered it wouldn't do 00. I had to do it separately and fortunately there were not many of them. Lucky, I guess.

Along the way was a lot of frustration trying to make it do what I wanted it to. I knew perfectly well it could, since I'd often done this sort of stuff in earlier versions. It annoys me that text typed into a field behaves one way, and copied AS A VALUE DAMMIT behaves differently. Plus that damn ribbon pisses me off.

Look. I don't care how you say the date. Humans are pretty good at figuring out dates given the subtle clues of human communication. But in my world, there is only one way to write a date, and everybody else is wrong except for me. Today is written 20120720. You'll see lots of my work files with that sort of date in the file name, for sequential versions of complicated files. Why no, since you ask, I don't trust Windows to track versions.

Why do I like this date format? It sorts things properly, especially in rows of data. It doesn't waste space by putting in dashes or slashes. It doesn't mix numbers and letters. Even given 20010102, most people are going to grasp that it's year month day. Most importantly, XL isn't going to screw with it. But a string 01-02-01? WTF? What can you do with that? With different countries disagreeing about what order the numerals are in? Let's get a grip people and stop wasting time. Dates, the metric system, guns, and political reform, America needs to get with the program.

If I must, I really must work with dates so I can do date arithmetic, I will, but I'll put those columns somewhere else, and I'll hide them. Then I'll check the results with an app on my phone, because I don't trust xl when it comes to date arithmetic. I once had a file that had started life as a Mac file, or someone had chosen some weird setting in their version of xl, so the date serial numbers started someplace else. I found that only because I got lucky and I'd been burned by that sort of thing so I knew to look.

It makes me nuts when I'm working in XL with ordinary numbers, and I'll write something like 1020 because that's how many of something there is. XL assumes it's a date and turns it into Oct-20, or something else. Then my blood pressure goes up a bit as I fix it. I hate extra formatting. My spreadsheets are not pretty. In one sense I hate that XL can filter by formatting now, since that's what people do, rather than put in a few extra filter columns and IF statements, the way we were intended to.

Yesterday I was mapping some columns of data comparing which mapped to which. One went out to CS, so we're talking lots of columns. I had the field names in columns in another tab, because that makes them easy to work with, and I was trying to label the field names with the column they were in. Imagine my surprise when it wouldn't autofill the letters of the alphabet. Imagine my further surprise when there wasn't a work around that would gracefully move on to AA and beyond. Sigh. No, I didn't manually letter it. I had to figure out my own way of doing it. Which is why I get paid what I do, I figure stuff out.

The other autocorrect that had me cursing was Word. I don't much like Word at the best of times, whereas most of the time I kind of like XL. Word, no matter how I played with the spell check settings was insisting, INSISTING on changing IHS into HIS. Every effing time. Even though I had told it not to change uppercase words. More sigh.

In some senses it's nice when they add features to an application. Some of them are handy. I like how XL finds duplicates, though I'm not so pleased that it marks duplicates with formatting that can be found by filtering. I would have done it differently. (The ONLY exception to the above remark about filtering and formatting.) That makes my life easier. But what makes me happy is that it doesn't do it till I ask. Why isn't that true for all the other stuff?

Why can't XL sit there docilely while I plug stuff into it, and then I can click a button called "XL be Helpful". Even then it doesn't actually change anything, all it does is highlights a cell and pops up a box. Is this a date and if so which format would you like it in and do you want the whole row or column done? Along with a series of appropriate response boxes. Here's the things XL thinks are spelling mistakes, are they really? For this kind of data, a auto format might make the data easier to understand, do you want a preview of it? You've got a formula in cell C45 that doesn't work, did you want a formula and if so do you want to troubleshoot it, or did you intend something else? And so on, leaving the user in control of what's happening on their spreadsheet or Word document.

Don't get me started on the different controls between those two programs to play with margins and the like. Just don't. And yes, I was getting very frustrated trying to find the control that lets me print just selected text. I know where it is now, and it's a pain in the ass. Looking for a shortcut there. At least IT fixed the printer driver error they produced when they remapped the printer servers.

I'm not going to get all senile and maudlin about how things were better in the good old days. Handy people used to be able to fix cars with a few simple tools, and the talented could get by with a hammer. Now you need to have a doctorate in car innards and thousands of dollars in computerized tools to be able to figure what is wrong with a car. Those old cars were also death traps on the road. When I learned to drive we were taught that a head on collision at over about 30 miles per hour was almost certainly fatal, and that doing almost anything else to avoid it was preferable. Now you read of people surviving head on collisions at fast highway speeds, and that going into the ditch might not be the smartest thing to do because of the unpredictable loads that might result. There is no way I'd drive my first car, a 66 Ford Falcon now, not compared to our Accord.

Software has come a long way, and has much longer to go. I'm still grumpy about autocorrect. I'm a good typist. I can tell when I've made a mistake and have some routines to hit backspace a few times and fix something. But now, autocorrect will turn it into something else just before I backspace, and my fix gets tacked onto something else, prompting another autocorrect fix, which totally screws with my typing and flow of words. Apparently the autocorrects hate the letter 'u'. My regular readers know why that annoys me. It's a plot, I tell you.

In many cases, properly done, a more complex world can make our lives easier. Apple has provided several brilliant, and I mean capital B brilliant examples of this. In some ways the iPad and the iPhone are the most amazing things that humanity has produced to date. But when this complex world goes wrong, OMG. Sometimes trying to diagnose a software problem is extremely difficult because it depends on where you started. The exact same sequence of steps might have a different result. This is why I take screen shots of my testing results, and why developers are sometimes driven to drink.

My personal theory is that computers know whether we know what should happen when we click a button on screen. If we know we did everything right, and we know what SHOULD happen, the computer will tug it's forelock and say "Yes sir, three bags full". But show the least hesitancy or uncertainty, and the computer will give you the finger at the very least. I've seen it again and again in software training. (Which reminds me of one data clerk who had the most delightful dance of rage when it happened to her. Talk about a potty mouth! I learned some new expressions.)

In some ways, I like this complicated world. I can sit looking thoughtfully at a computer screen, or a whiteboard covered in little squares and arrows and stuff, AND THEY PAY ME! If it wasn't for computers, who knows what I'd be doing for a living? Almost certainly something not as engaging to think about, and harder physical work.

There. I feel much better now. Even though my back doesn't, particularly.