Displaying 3/28/07 as 2007-03-28 in Excel

I had to prepare a table for a client showing the purchase dates of some of their older computers. I copied the dates out of my hardware inventory database and pasted them into Excel. They looked like this:

37210

37279

37432

37553

37553

37553

37729

37729

What that shows me is the number of days elapsed between the date of purchase and December 31, 1899. Not the most helpful display, is it?

What I wanted was a set of dates that appeared with the year, then the month, and then the day. That way they could easily be sorted chronologically, even if they were being sorted as text rather than as numbers. So I selected the column of numbers, went to the format cell command, and looked for the format I wanted. But all I had to choose from were the following examples:

3/14/2001

Wednesday, March 14, 2001

3/14

3/14/01

03/14/01

14-Mar

14-Mar-01

Mar-01

March-01

March 14, 2001

3/14/01 1:30 PM

3/14/01 13:30

M

M-01

3/14/2001

14-Mar-2001

None of those put the year first. So I had to create my own custom format. Here’s what it looks like:

Now my column of dates looks like this:

2001-11-15

2002-01-23

2002-06-25

2002-10-24

2002-10-24

2002-10-24

2003-04-18

2003-04-18

Much better.

If I’d wanted them to look like 20011115, I would have simply entered yyyymmdd. For 2001-Nov-15, it would have been yyyy-mmm-dd. You get the idea. Custom formats can come in very handy, and Excel gives you lots of examples to help get you started. Give it a try.