cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
qafnagy
Level I

How to properly format formula result as date?

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?

3 REPLIES 3
pmroz
Super User

Re: How to properly format formula result as date?

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

                )

        )

);

qafnagy
Level I

Re: How to properly format formula result as date?

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
Super User

Re: How to properly format formula result as date?

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.