How to sort dates in an Advanced View-to-Excel export


#1

When you export from Advanced View to Excel, dates are converted to the “string” data type. The reason for this is to preserve the date format and information as they appeared in Advanced View. However, this prevents sorting by date columns once in Excel.

The solution is to use a sortable date and time format such as ISO 8601, which will allow dates to be sorted lexicographically.

To do this:

  1. On the Advanced View search results page, click Edit columns.

  2. Click New column and give it a name.

  3. Right-click in the Expression box and choose the name of the column that contains the dates whose format you want to change.

  4. In the Format list, choose Date & time > Date (Custom) and enter a sortable format (e.g. yyyy-MM-ddThh:mm:ss for ISO 8601), then click OK.

  5. If you want, you can hide the original date column by clicking the pencil icon in the column header and choosing Hide column.

For information on the ISO 8601 standard, see http://www.iso.org/iso/home/standards/iso8601.htm.