Split date time cell value

[KelvinUK]

This should be simple, but my stroke affected brain just cannot work it out!!

I have a column of cells that I have formatted a ddmmyyy hh:mm. However, I need to extract the time into a separate column. 

The reason I want to do this is so that I can correctly sort the rows by the date then the time. 

If there is a way of sorting the datetime column based on the values, that would solve the issue. Unfortunately, sorting on this column gives a complete mix-up of results.

Thanks in advance.

Kelvin

[#2]

@KelvinUK 

If the values in the column are 'real' date/time values formatted as ddmmyy hh:mm, they should sort correctly.

If, however, they are text values that only look like dates/times, changing the number format shouldn't have any effect. You can use Data > Text to Columns to split the data:

• Select the text values in a column.
• On the Data tab of the ribbon, click Text to Columns.
• Select Delimited, then click Next.
• Select Space as delimiter, then click Next.
• For the first column, select Date, and select DMY from the drop down.
• Click Finish.

[#3 · Accepted solution]

@KelvinUK 

Here is a small example, inserted in the file. At the same time, additional suggestions for solutions and information

example 2:

=TEXT(A1,"h:mm:ss")

examble 3:

=TIME(TEXT(A1,"h"),TEXT(A1,"m"),TEXT(A1,"s"))

examble 4:

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

Additional Information:

HOUR function

Nikolino

I know I don't know (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

Thanks to all, now resolved @NikolinoDE