UC San Diego SearchMenu

Excel handles dates interestingly

Excel stores dates as a serial number that represents the number of days that have taken place since the beginning of the year 1900. This means that January 1, 1900 is really just a 1. January 2, 1900 is 2. By the time we get all the way to the present decade, the numbers have gotten pretty big… September 10, 2013 is stored as 41527.

From The Definitive Guide to Using Dates and Times in Excel

This can allow you to do calculations on dates (days elapsed between two dates) but generally makes the kind of data manipulation we do with dates (formatting, syntax, etc.) more difficult.  So, for the majority of metadata situations it's better to have your dates stored as "Text" format type value rather than "Date" format value.  So instead of storing "41527" for the example above, the value would be "September 10, 2013".

If you have Excel "Date" formatted values, you can convert them to "Text" formatted values using this formula:

=TEXT(A2, "yyyy-mm-dd")

In the example above, the first part of the argument (A2) refers to the cell with the Date formatted value.  The second part of the argument ("yyyy-mm-dd") tells how to format the text value, and can be replaced by other formats (see here for more options). 

The two formats we use most commonly are:

yyyy-mm-dd would convert 41527 to 2013-09-10

mmmm dd, yy = would convert 41527 to September 10, 2013


If you apply this formula and the source cell (A2 in the example above) is empty it will give you a resulting value of 1900-01-00 instead of a null/blank value.  To avoid this, the following formula only outputs a converted text value when the source cell is not null: