topic Re: Convert Character Timestamp to Numeric Date in Discussions

Convert Character Timestamp to Numeric Date

Sarah_Gagne — Tue, 15 Apr 2025 14:56:45 GMT

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'?

Re: Convert Character Timestamp to Numeric Date

jthi — Tue, 15 Apr 2025 15:20:31 GMT

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

Re: Convert Character Timestamp to Numeric Date

txnelson — Tue, 15 Apr 2025 15:28:54 GMT

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 ) )
)