When you’re talking about formatting dates, you’ve got to lead with this xdcd comic:

xkcd comic on date format
xkcd.com

Excel does crazy things with dates. It stores them internally as an integer, counting the days since 1900-01-01. Wait that’s only for Windows; on Macs, it counts the days since 1904-01-01.

So, be careful to check that the dates haven’t been mangled when your data come out of Excel.

Excel also has a tendency to turn other things into dates:

first oct-4 tweet

second oct-4 tweet

Personally, I’d be inclined to use a plain text format for columns in an Excel worksheet that are going to contain dates, so that it doesn’t do anything to them:

  • Select the column
  • In the menu bar, select Format → Cells
  • Choose “Text” on the left

Alternatively, you could create three separate columns with year, month, and day. Those will be ordinary integers, and so Excel won’t mess them up.

But really what I wanted to emphasize here: be consistent in the way in which you write dates. And really, always use the YYYY-MM-DD format, as in the xkcd comic, above (or put the year, month, and day in separate columns, if you want).

I have an old Excel spreadsheet in front of me that looks a bit like this:

<?xml version=”1.0” encoding=”UTF-8”?>

38.6 1/11/2006 8 46.1 1/11/2006 7 52.9 e 6 45.7 e 5 47 e 12/6/2005 4 45.3 12/9/05 3 54.9 12/9/05 2 Weight Assay date Date 1 C B A

I don’t quite remember what those e’s were for, but having different date formats within a column can cause headaches later.

Use care about dates, and be consistent.


Next up: Fill in all of the cells.

(Previous: Be consistent.)