How to properly format formula result as date?

Question

Hi,

I need to create a Date field in a data set imported into JMP. In the original data, the date is embedded in the date-time format YYYYMMDDhhhhmmss. I wrote the following simple formula and attached it to a new column:

Munger(:DATE, 0, 8).

The result looks OK but when I change the column format Numeric > Continuous > Date > yyyymmdd, the values change to the format “1904….”.

I thought maybe I need to first convert the data type to numeric, so I added Num to my formula: Num(Munger(:DATE, 0, 8)). It still didn’t work.

I tried the ‘workaround’ in Knowledgebase Note 6149 for both cases (with and without 'Num')—it still didn’t work.

I'm using JMP 10.

What am I missing? How do I get JMP to treat the result of my formula as the correct date?

pmroz · Answer

Sorry - you're correct.  If you change all occurrences of ddMonyyyy to yyymmdd it will work for you.  If you want your "Real Date" column to be displayed like 12Jan2005 then only change the occurrence of ddMonyyyy in the Informat statement.

qafnagy · Answer

I appreciate your reply.

I’m little confused by your formatting statement in the formula:  Format( "ddMonyyyy"). As my processed data are in the format yyyymmdd, will JMP recognize it? Or can I do something like Format( "yyyymmdd")?

Thanks for any tips.

pmroz · Answer

I would call your column something other than DATE - it's not a function by itself but it's pretty close to being a reserved word.  Suppose your input datetime string is in a column called TEXT DATE.  The following formula will do the trick:

dt << New Column( "Real Date", Numeric, Continuous,

Format( "ddMonyyyy", 10 ), Input Format( "ddMonyyyy" ),

Formula( if (!isempty(:Text Date),

Informat(Substr( :Text Date, 1, 8 ), "ddMonyyyy")

)

);