One of the issues I have encountered in Excel when I am parsing data before analysis is being able to format the date and time from a column. One engine in particular outputs a report that embeds the time of transaction along with the date (e.g. 04/07/2008 08:08AM). Even if you change the cell formatting to display only MM/DD/YYYY, the pesky 00:00 becomes invisible but still remains attached in the background, making the data impossible to pivot.

Assuming that the dates you are trying to format are in Column A, the solution I have arrived at is to insert a new column, and copy this formula all the way down:

=MONTH(A1) & “/” & DAY(A1) & “/” & YEAR(A1)

This effectively extracts only the date and time from Column A. The next step is to copy all of the data in Column B and paste over it as values, as to remove the formulas. Et voila, you’ve got a clean set of dates with no times.