Two Strategies for Working with Dates in Excel

One of the periodic discussions that comes up as a data management evangelist (beside the “data is” v. “data are” debate) is about how Excel is terrible with dates. Excel mangles dates, mangles non-date data into dates, and doesn’t play nice with date interoperability in other spreadsheet programs. So how does one begin to impose order on dates in Excel?

Anyone who has spent enough time hanging around me knows that I love the international date standard ISO 8601, where dates are represented as YYYYMMDD (or YYYY-MM-DD). So one way I work with dates in Excel is to put them in this format. Unfortunately, this is not a default option in Excel and must be done through custom formatting. To do so, highlight the cells to reformat, click on “format cells”, scroll down to “Custom”, and enter “YYYY-MM-DD” in the box. This works okay, but sometimes Excel forgets to keep this formatting such as when entering data into an empty cell in a reformatted column or copying and pasting dates into a formatted cell. I still find it’s better than Excel defaults because ISO 8601 is superior to everything else.

Edited at add (2017-07-31): I just realized another way to set up ISO8601 in Excel and that’s simply as a 8-digit number. For example, July 31, 2017 becomes the number “20170731”. Excel recognizes this as a number and not a date, so Excel won’t try to do weird conversions on it. The downside of this method is that you’ll have to process this number to turn it back into a usable date, but at least that date will be accurate!

Honestly, the best thing to do is to break dates into three columns in Excel: one for year, one for month, one for day. This totally avoids giving Excel anything that even remotely looks like a date. Even better, there are built-in Excel functions that allow you to break a “normal” date up into its component parts. For example, if you have a normal date in cell A1, you can use the following functions in the next columns to isolate its individual parts:

use “=YEAR(A1)” to calculate year
use “=MONTH(A1)” to calculate month
use “=DAY(A1)” to calculate day

With functions in place in one row of data, it’s easy in Excel to apply them to a whole spreadsheet of dates. (Note that if you’re really worried about Excel mangling your dates, you may want to copy values these three columns and re-paste them as raw numbers instead of as a formula to truly separate your values from anything related to their original formatting.)

So that’s one strategy to make working with dates in Excel a bit tidier and another to prevent your dates from getting totally messed up by the program. I hope these two tips are helpful!