Convert text string to date & time help

[iantix]

Hi all,

Having trouble with formula to convert a text string to a time & date.

Have attached a sample - am almost there but it's not working.

I think the AM is screwing things up - anyone help please?

Ian

[Special-K]

=mid(a2,5,20)+0

or probably

=TEXT(MID(A2,5,20)+0,"dd/mm/yyyy hh:mm")

[Root_]

Try using the following formula and formatting the cell as m/d/yyyy h:mm

=--REPLACE(A2,1,4,"")

[Phuocam]

Try this ...

=TEXT(REPLACE(A2,1,4,""),"dd-mm-yyyy hh:mm")

[JohnTopley]

Try

=DATEVALUE(MID(A2,4,12)) + TIMEVALUE(RIGHT(A2,8))

[iantix]

Thanks all - I started with the most recent solution posted by Phuocam and that worked perfectly. Will mark as solved.

[AliGW]

Root's is more efficient, as it will leave you with a proper data and time. Phuocam's will leave you with text.

[sandy666]

you've ex365 so you can try PowerQuery, just 2, maybe 3, clicks

[iantix]

I tell a lie, for some reason, John's solution worked best as the one I first used didn't lock a true time value on it (i.e. when I saved as CSV, it didn't switch to numbers, as I expected it would) whereas John's did. Thanks John!

[iantix]

Root's is more efficient, as it will leave you with a proper data and time. Phuocam's will leave you with text.

Thanks, yes, just discovered this and posted.

[cbatrody]

OR,

Try the following in C2:

=--RIGHT(A2,LEN(A2)-4)

[iantix]

Marked solved now, thanks all