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?

i get the timestamps from a text file so they are strings, representind date and time — fjohn
– fjohn, Commented Dec 10, 2021 at 23:35

Spinner · Accepted Answer · 2021-12-09 23:54:14Z

1

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

edited Dec 9, 2021 at 23:54

answered Dec 9, 2021 at 23:36

Spinner

1,0981 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2021-12-10 02:01:15Z

1

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

answered Dec 10, 2021 at 2:01

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

2 Comments

fjohn Over a year ago

it is not working for me

Ron Rosenfeld Over a year ago

@fjohn As you can see from the screenshots, it works here. I suspect there is either an issue with your data which I did not appreciate from your example, or you have not properly applied the solution I showed.

Collectives™ on Stack Overflow

Excel sort values by date and time

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related