Converting 01.01.01 date to correct format

**Dave350z** · 06-27-2012, 09:37 AM

Hey folks,
Quick one, how do you convert the below date format to 01/01/2011 (I don't need the time converted as I don't need it)

24.04.2008 15:56:57

Cheers
Dave

**jeffreybrown** · 06-27-2012, 09:42 AM

Hi Dave,

This should work

=SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),".","/")+0

**tom1977** · 06-27-2012, 09:42 AM

Hi choose "text to columns" command (after choosing this command click 2 times "next" button then choose date "DMY" and click finish button)

**Dave350z** · 06-27-2012, 09:53 AM

Thanks, substitute formula wouldn't work

Text to columns also didn't work as above, I had to have the delimiter as Space rather than just Tab and then it converted it to DMY...

Cheers..

**jeffreybrown** · 06-27-2012, 09:56 AM

We are glad to help and thanks for the feedback,

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save