CODEWITHSUNDEEP
exceldate
jkp1187
jkp1187

Reputation: 169

Exported dates not sorting correctly

I'm using an application called Kroll OnTrack to manage a review of documents as part of a litigation project. Kroll can export the metadata for these files into an XLS file. The problem is that when Kroll exports the dates for these documents, it seems to do so as a string, and the dates are being exported in MM/DD/YYYY format, so when I go to sort by date, the documents line up in this order:

01/01/2005
02/02/2005
03/05/2010
04/07/2006
05/03/2007

...and so on.

I need to be able to sort the table by date so we can put together a chronology of these documents. Trying to force Excel to re-format as a date doesn't seem to work, nor have I had any luck using DATEVALUE(). Right now, I'm doing text-to-columns then re-concatenating the dates, which seems to work, but is there a more elegant/efficient solution for this issue?

Upvotes: 0

Views: 2890

Answers (2)

Rusan Kax
Rusan Kax

Reputation: 1894

In a column adjacent to your text dates, you could try something like this

DATE(RIGHT(E3,4),LEFT(E3,2),MID(E3,4,2))

Then copy down and sort/filter on this column of real dates.

Where cell E3 contains you text-date. You could also follow up with Text(...,"yyyy-mm-dd") or some other date format, if you need the date in a specific format.

The formula assumes that the text string contained in cell E3 is of length 10 (i.e. all white space is trimmed and padded zeros for day and month are used). If this is not the case, the forumula is easily amended.

Upvotes: 0

user4039065
user4039065

Reputation:

Perform Text-to-Columns on that column of dates but with some optional commands.

  1. On the first screen of the T2C wizard, choose Fixed Width and click Next.
  2. On the second page of the wizard, discard any border that Excel added by dragging them up out of the Data Preview window. It is very likely that none will be there. Click Next.
  3. On the third page of the wizard, choose Column data format, Date and MDY from the drop-down selector beside Date.
  4. Click Finish.

You should be left with real dates in the column. This procedure is easily recorded for future use.

Upvotes: 1

Related Questions