topic Re: transform column in Discussions

transform column

NetflixCrow956 — Fri, 09 Jun 2023 16:59:57 GMT

Hello everyone, I have a problem when I want to transform this column into a date. I transform it in Numeric then in date but when I transform it in date the year becomes 1904 and I don't know why?

Re: transform column

jthi — Wed, 25 May 2022 09:32:17 GMT

You could try creating a formula column with Informat() and then format that as you want. If the start value isn't character add Char() around the column reference

Informat("20210408", "YYYYMMDD");

Informat(Char(:ColumnToConvert), "YYYYMMDD");

Re: transform column

Mark_Bailey — Wed, 25 May 2022 12:38:28 GMT

The reason is that JMP uses seconds since 12:00 AM January 1, 1904, as its base for time values. Your numbers are integers that code the year, month, and day. JMP has many built-in functions for working with date and time values. That is why @jthi's solution is so simple.

Re: transform column

Jeff_Perkinson — Wed, 25 May 2022 13:30:36 GMT

@jthi and @Mark_Bailey have given you some good information. If you'd like to know more about how JMP handles dates see:

Re: transform column

NetflixCrow956 — Wed, 25 May 2022 15:02:08 GMT

I know but when I do, nothing happens

Re: transform column

txnelson — Wed, 25 May 2022 15:18:27 GMT

Try

column(dt, "Operation Start Date" ) << Input Format( "yyyymmdd");

Re: transform column

jthi — Thu, 26 May 2022 07:17:16 GMT

Create new formula with the formula I provided earlier, you should end up with something like this:

Then change the Format to what fits you best

You can do this in place, but I would suggest:

Creating new formula column
Changing Format
Verifying everything works as you want to
Deleting old column if you don't need it
Renaming new column

If you have to do this often, then I would look into using JSL and scripting it (I think Activity Recorder/Enhanced Log in JMP16 can record all of these steps to make scripting very simple).