Help in splitting a non stand excel date and time

[Jagunjunior]

11 July 2014 12:00:00

I have a column with data an time  in the format above ,I am finding it difficult to split

I have tried INT fuction, text to colum function ,concatenate function still not getting the fix.

Please help

[#2]

@Jagunjunior 

I guess it is with the format "dd mmmm yyyy hh:mm:ss"

You can either 

Change the format of the cell (CTRL+1)

or 

Use a formula to meet your needs (Considering A2 contains the date)

=TEXT(A2,"mm/dd/yyyy")  Resulting in "09/06/2021"

=TEXT(A2,"h:mm:ss") 12:00:00

Remember that using formula you are converting the values to a text value.

So I would suggest you just make reference to the Date cell and apply the desired format.

[#3]

@Jagunjunior 

Let's say you have such a date in A2.

To extract the date, enter the formula =INT(1*A2) in a cell and format that cell as a date.

To extract the time, enter the formula =MOD(1*A2,1) in a cell and format that cell as a time.

Alternatively, use =DATEVALUE(A2) and =TIMEVALUE(A2)

[#4]

clt + 1 does not do anything used it before

[#5 · Accepted solution]

CTRL+1 is a shortcut to access the Format Options.
There you can select or create your custom format for the cell you will apply to.

As I said, if you just want the date format only make reference to the cell where there is a full date format =A2 and apply your desired format using the format Option accessed by CTRL+1

[#6]

Thank you so much ,it worked like a charm

[#7]

Thank you for your help

[#8]

Thank you for your help ,worked as well