Jan-48

n9ds · 2008-04-04

The cry frequently heard around our house, usually directed at a popular software package - "STOP HELPING ME!!"

2008-04-04

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

mallard · 2008-04-04

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.

yet another Matt · 2008-04-04

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.

2008-04-04

mallard:
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.

I would say the real problem is not the formatting, as this can be fixed (see example below).

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>

maldrich · 2008-04-04

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.

Zylon · 2008-04-04

Christopher:
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.

Ahem.

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

2008-04-04

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.

Jan-48

Featured Comments