My mother-in-law was making a list of all the headstones in a local cemetary to post online. The data was tabular, so it made sense to record the data using a spreadsheet. She and a friend dutifully entered the list of all several-hundred-odd names, dates, and places, only to discover that the spreadsheet application completely changed those dates that contained only the year (where the month and day were unknown).

It turns out that when a single integer is entered in a date-formatted cell, this particular spreadsheet application converts the integer to a date by adding that number of days to the earliest representable date (Jan. 1900). Thus, for the year 1899, my monther-in-law ended up with a date in March 1905.

This seems like a pretty tenuous assumption to make. If I enter 1899 as a date, shouldn’t it instead assume that the date I want is 1899 (where the day and month are unknown), instead of some arbitrary date 1,899 days from Jan. 1, 1900? It is pretty obvious that in this case, the programmers just shrugged and made a decision without really thinking what would be most likely, and wound up with a “solution” that is completely counter to what the user expects.

When writing software, it is often tempting to choose a solution that is simple to implement, especially when a deadline looms. (“Instead of changing the application to support incomplete dates, let’s just add this number to our earliest representable date.”) The problem with these kinds of solutions is that they are rarely what the user needs (or wants, or expects) which can result in a lot of frustration. In this case, my mother-in-law had to go through the document by hand and identify which dates were corrupted by this “feature”. (It turns out that she could have just changed the format of the column to text, instead of date—the data itself was not altered, just its representation—but again, that’s hardly intuitive to someone who is not very familiar with spreadsheets.)

Be predictable. Think like your users. Better yet, be your own user. It’ll be much rarer that you’ll come up a non-solution like this one.