Formula to take the time out of a date cell and reformat said date

[C_P]

A colleague of mine asked me how to do this and it sounded pretty simple. However, it proved to be a little tricky - though eventually he found a lengthy work around. Just wondering whether there is an easy way to do this.

He imported a set of data into an Excel spreadsheet where the date appeared as such:

Jan 3 2013 12:00

So: month, a double space, the day, space, time.

He wanted the date in the cell to read like this (in the UK format rather than the US):

03/01/2013
(day/month/year)

Eventually, using a combination of concatenate and trim to get rid of the spaces, he was able to do this. But just wondering whether there was a much simpler way that we might have overlooked? I believe this is something he will need to do again in the future.

Any help would be greatly appreciated!

[Pepe Le Mokko]

A possibility:

select the column with dates - Data - Text to columns
Select Fixed width
Remove all column breaks except the last one ( perhaps move it a bit)
Next
Select the first column
Select Date and MDY from the dropdown
Select the second column and select " Do not import column"
Finish

[:) Sixthsense :)]

In A1 Cell
Jan 3 2013 12:00

The below formula will work fine if your system date setting is MM-DD-YYYY

Please Login or Register  to view this content.

[C_P]

Thanks for the swift response, both of you!

Ill pass on both possibilities to him and hopefully they will prove easier to use.

[Kevin UK]

Hi C_P

Assuming the data is in A1
=--SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",50)),50,100))," ",LEFT(A1,FIND(" ",A1)-1))