Convert Text date to date

[HealthonCall]

Hi,

I have a survey which outputs a csv file. In one of the fields it returns

August 3, 2018 10:58 AM - Text field


I am wanting to convert it in a table to date format, I have tried =date(cell) but it does not seem to work... any ideas?

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!
Try
=LEFT(REPLACE(SUBSTITUTE(A1,",",LEFT(A1,FIND(" ",A1))),1,FIND(" ",A1),""),LEN(A1)-9)+0

or
=DATEVALUE(SUBSTITUTE(MID(SUBSTITUTE(A1," ",REPT(" ",20)),20,40),",",LEFT(A1,3)))

 

[HealthonCall]

Hi Peter,

Thanks will give that a try.

 

[HealthonCall]

Hi Peter,

That is putting the data in the correct order in ie returning 03/08/2018 ... however if I do a column next to it and reference it using =YEAR(cell) it returns
10/07/1905 ??

Any idea?

Thanks

Chris

 

[HealthonCall]

Solved it with

=TEXT([@Date],"YYYY")

Thanks for help.

 

[Peter_SSs]

however if I do a column next to it and reference it using =YEAR(cell) it returns
10/07/1905 ??

Any idea?

All you would have to do is format that cell/column as Number instead of Date. Dates in Excel are just whole numbers starting from 1 January 1900. Your YEAR() formula was correctly returning 2018, but because your cell was formatted as Date it was showing 10 July 1905 as that is 2018 days counting from 1 January 1900.

Your other solution is fine so long as you are happy with a Text value of "2018" rather than the Number 2018.