Excel sort values by date and time-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

To convert the strings to "real dates" which can be sorted in date/time order, you can use this formula to create a Helper column

=DATE(MID(A1,7,4),MID(A1,4,2), LEFT(A1,2))   TIMEVALUE(RIGHT(A1,12))

This formula will work independently of the Windows Regional Settings on the computer. It obviously assumes the source data is DMY, but will convert the string to a real date even if the WRS on the target compute is something else.

Original Data

Sorted

The helper column can be hidden, or deleted after the sorting is done