CODEWITHSUNDEEP
excel-formula
Nik
Nik

Reputation: 1

Convert a timestamp into excel readable format?

I am trying to convert a column in my excel sheet which has the value like: Jan 31 2016 11:41PM, I need to convert it into excel readable format like: 1/31/2016 11:41:00 PM.

I tried to convert by reformatting the column in excel and several other alternatives but doesn't work.

Please help! Thanks.

Upvotes: 0

Views: 321

Answers (2)

Rosetta
Rosetta

Reputation: 2725

try this

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"PM"," PM"),"AM"," AM")," ",", ",2)

the output is a datetime value.

Then format it using number formatting to your liking.

here is a break down view of the formula enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Use this:

=--SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,FIND(" ",A1),FIND("}}}",(SUBSTITUTE(A1," ","}}}",2)))-FIND(" ",A1))) &" " & LEFT(A1,3) & MID(A1,FIND("}}}",(SUBSTITUTE(A1," ","}}}",2))),LEN(A1)),"P"," P"),"A", " A")

enter image description here

Then using a custom Format of:

mm/dd/yyyy hh:mm:ss AM/PM

enter image description here

you get a number disguised as a date time:

enter image description here

Upvotes: 1

Related Questions