Write dates like YYYY-MM-DD
When you’re talking about formatting dates, you’ve got to lead with this xdcd comic:
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:
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
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”?>
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.)