Formula for return date/time from text in cell.

Hello All,

I was struggling with this for some time and after some help on a post I was able to come up with this formula "=(DATEVALUE(LEFT(XX,10)))+(TIMEVALUE(MID(XX,12,5)))" where "XX" is the cell in reference. I've come across many other forums with people asking the same thing and this, to me, seems to be the simplest answer. What this will do is return text that is copied in from this format "2014/11/20 11:53 EST" and turn it in to a Date/Time format so it can be sorted oldest to newest. It works with short date/long date and if you want the time just format the cell to display time and it will show. Hope this helps anyone who goes searching around like I did.

Thanks!

If this is always your format, Simplest way personally would be =Value(Left(XX,10)) for date and =Value(Mid(XX,11,5)) for time. Then just format the cell to display the format you wish to see them in that way you can utilize them in their actual value formats.