Excel sort values by date and time

Question

I have 5 columns with values (and many rows). First column is timestamp in the format dd.mm.yyyy hh:mm:ss.000 The last three zeros indicate miliseconds, e.g. 09.12.2021 22:00:34.343

We use this kind of date format (day first before month).

If I try to sort them in Excel , they get sorted alfabetically and not chronologically.

For the values below

23.10.2021 20:59:47.066

23.10.2021 21:07:17.061

23.10.2021 21:17:17.082

23.10.2021 23:42:18.008

23.11.2021 11:11:00.005

23.11.2021 11:21:00.096

24.10.2021 00:32:18.052

24.11.2021 16:42:14.046

I need to get

23.10.2021 20:59:47.066

23.10.2021 21:07:17.061

23.10.2021 21:17:17.082

23.10.2021 23:42:18.008

**24.10.2021 00:32:18.052**

23.11.2021 11:11:00.005

23.11.2021 11:21:00.096

24.11.2021 16:42:14.046

What workaround is for this?

Spinner Spinner · Accepted Answer · 2021-12-09T23:36:57

One solution (if the Timestamps must remain as string values):
Add a helper column that converts the string values to sort-able date/time values. The formula for that would be:

=DATEVALUE(SUBSTITUTE(LEFT(A1,10),".","/"))+TIMEVALUE(RIGHT(A1,12))

Note:
a) The above is of course converts the value at cell A1
b) It assume the format is always dd.mm.yyyy hh:mm:ss.nnn

Sample Result (sorted on column B) In case you’re wondering, the Number Format for Column B is dd.mm.yyyy hh:mm:ss.000

Excel sort values by date and time

1 Answers