Forum Discussion
Split date time cell value
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
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:
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.
3 Replies
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:
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.
Thanks to all, now resolved NikolinoDE
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.
