how do you convert strings to Posixct time format in excel

Question

I have this field in excel:

16JUL15:08:22:13

I would like to convert this to POSICct format like this:

2015-07-16 08:22:13

How would I do this in excel?

Answer 1

In an Excel worksheet, you really want to convert that text-that-looks-like-a-date into a real date and then you can do anything you want with how it looks (aka format).

Use the native worksheet REPLACE function to swap out the colon in the 8^th position for a space and add a double unary to achieve a true date. Excel's overhead will take care of the rest.

=--REPLACE(A2, 8, 1, CHAR(32))

Complete the operation by formatting the cell as Custom with a format mask of yyyy-mm-dd hh:mm:ss.

  

Answer 2

Use this formula:

=TEXT(--SUBSTITUTE(A1,":"," ",1),"yyyy-mm-dd hh:mm:ss")

The issue is the first : between the time and the date. By removing that it can become a number that can be read as a date.