Timestamp reads as text when converting to Date

[Data can be tough AP]

Hello, I have been trying different ways to create a Date on this timestamp which usually involves me using a DATEVALUE+TEXT combination which generally works and converting the timestamp field to General (currently is set to Date when just the raw file). However, no matter how I split the values I keep getting a #VALUE error which seems to mean the field keeps reading as text. The goal was to get the "Date of Access" field in column I to be a workable date but I cant figure out how to fix the timestamp.

Would you have any guidance?

Thank you,

 

[Joe4]

Yep, column E looks like a text entry to me.
Try this formula:

=DATEVALUE(LEFT(E2,6) & ", " & MID(E2,8,4))

and format your the cell in your desired date format.

 

[Alex Blakenburg]

I think @Joe4's formula will only work if your default format is US. Try the formula that @Cubist came up with:

=--TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(E2," "),0,{2,1,3}))

Original ref: Convert timestamp format to DD/MM/YYY

 

[Data can be tough AP]

Yep, column E looks like a text entry to me.
Try this formula:

=DATEVALUE(LEFT(E2,6) & ", " & MID(E2,8,4))

and format your the cell in your desired date format.

Thank you for this, it didnt quite work but thank you for you help

 

[Data can be tough AP]

I think @Joe4's formula will only work if your default format is US. Try the formula that @Cubist came up with:

=--TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(E2," "),0,{2,1,3}))

Original ref: Convert timestamp format to DD/MM/YYY

Yes this worked! Thank you so much for the help.

 

[Alex Blakenburg]

You're welcome. Glad we could help.

 

[Joe4]

Thank you for this, it didnt quite work but thank you for you help

Looking at the examples you posted, I thought it looked like you might be working on American system, but looks like I guessed wrong!
Glad Alex came along and posted a solution that would work on your system.