SOLVED

Split date time cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-2489397%22%20slang%3D%22en-US%22%3ESplit%20date%20time%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2489397%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20should%20be%20simple%2C%20but%20my%20stroke%20affected%20brain%20just%20cannot%20work%20it%20out!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20of%20cells%20that%20I%20have%20formatted%20a%20ddmmyyy%20hh%3Amm.%20However%2C%20I%20need%20to%20extract%20the%20time%20into%20a%20separate%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20I%20want%20to%20do%20this%20is%20so%20that%20I%20can%20correctly%20sort%20the%20rows%20by%20the%20date%20then%20the%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20way%20of%20sorting%20the%20datetime%20column%20based%20on%20the%20values%2C%20that%20would%20solve%20the%20issue.%20Unfortunately%2C%20sorting%20on%20this%20column%20gives%20a%20complete%20mix-up%20of%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKelvin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2489397%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2489459%22%20slang%3D%22en-US%22%3ERe%3A%20Split%20date%20time%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2489459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089197%22%20target%3D%22_blank%22%3E%40KelvinUK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20values%20in%20the%20column%20are%20'real'%20date%2Ftime%20values%20formatted%20as%20ddmmyy%20hh%3Amm%2C%20they%20should%20sort%20correctly.%3C%2FP%3E%0A%3CP%3EIf%2C%20however%2C%20they%20are%20text%20values%20that%20only%20look%20like%20dates%2Ftimes%2C%20changing%20the%20number%20format%20shouldn't%20have%20any%20effect.%20You%20can%20use%20Data%20%26gt%3B%20Text%20to%20Columns%20to%20split%20the%20data%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20the%20text%20values%20in%20a%20column.%3C%2FLI%3E%0A%3CLI%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20click%20Text%20to%20Columns.%3C%2FLI%3E%0A%3CLI%3ESelect%20Delimited%2C%20then%20click%20Next.%3C%2FLI%3E%0A%3CLI%3ESelect%20Space%20as%20delimiter%2C%20then%20click%20Next.%3C%2FLI%3E%0A%3CLI%3EFor%20the%20first%20column%2C%20select%20Date%2C%20and%20select%20DMY%20from%20the%20drop%20down.%3C%2FLI%3E%0A%3CLI%3EClick%20Finish.%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

3 Replies

@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.
best response confirmed by KelvinUK (New Contributor)
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.

Thanks to all, now resolved @NikolinoDE