Convert Character Timestamp to Numeric Date

Question

I've read through past discussion posts trying to find a solution to converting a timestamp that is character/nominal into a date that is numeric/ordinal. So far I've tried the following approaches without success:

* Reimporting the .CSV with numeric/ordinal selected
* Formula using informat and format
* Recoding

Does anyone have a formula suggestion that I can use to transform 'Timestamp' into the format identified in 'Ideal Timestamp'?

Timestamp Troubleshoot Example.jmp

Timestamp.JPG

txnelson · Answer

Here is one way to handle this.

Create a numeric column and set the format to be displayed to

yyyy-mm-dd

Then set the columns formula to

Date MDY(
	Num( Substr( :Timestamp, 5, 2 ) ),
	Num( Substr( :Timestamp, 7, 2 ) ),
	Num( Substr( :Timestamp, 1, 4 ) )
)

jthi · Answer

Use Word + In Format

In Format(Word(1, :Timestamp, " "), "YYYYMMDD")

You can also try to play around with Format Pattern but it might not work due to milliseconds. If you wish to have the time left, use word with "." separator and modify the format for informat as necessary