Strip date from date/time cell, format correctly so it's recognised as a date by Excel?

[robdav]

Hi

I get sent a csv file with a cell containing the date and time like this "30.11.2017 17:40:41", remember I'm in England so dd/mm/yyyy is the format we use.

If I use the two commands below I can get just the date as 30/11/2017 but Excel doesn't recognise it as a date until I click in the cell and then click out off the cell. Not quite sure why but I don't want to do that for every cell.

Is there a better way to do this or at least a way to get Excel to recognise it as a date?

Many thanks

Rob

=LEFT(A1,LEN(A1-9)
=SUBSTITUTE(A1,".","/")

 

[Scott T]

This will turn it into a date. format cell as date

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

 

[robdav]

Perfect. Thank you.