The World Statistics Day celebration continues here in the Community. We all need reliable data for sound decision making. Do you have a data source that you trust most? Head over to Discussions to tell us about it.
Choose Language Hide Translation Bar
Highlighted
Marc3
Level II

change date format

Hi, 

 I received an .xlsx data sheet with the following date "dd.mm.yy" in a column, i.e. "03.10.14".

After copying the data into JMP I created a new column to get the numeric number for the date and converted the date into 1. a numeric date type with

2. a ordinal modeling type and

3. format to "yyyy-mm-dd". 

 

However, some dates "20.10.15" do not appear in my new column. Is there a defnition conflict?

 

Please advise how I can overcome this issue. 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
txnelson
Super User

Re: change date format

Your issue is that you are reading the initial date field wrong.  The initial date field is in the format "dd.mm.yy" and you are reading it as if it is "mm.dd.yy".  I have attached your data table where it can be seen that the numdate value does not match your newdate value.  In the data table, I have created a Character Date column, which uses a format() function in the formula to come up with the character value.

 

 

Jim

View solution in original post

Highlighted
Marc3
Level II

Re: change date format

I see the step that was needed to complete the conversion.
Problem solved. Thank you!

View solution in original post

12 REPLIES 12
Highlighted
txnelson
Super User

Re: change date format

I have some questions:

  1.  How do you get your xlsx data into JMP?  You say "After copying".  Is this a copy and paste into new JMP data table?  Have you tried just a      File==>Open     and then pointing to the xlsx file and opening it that way?
  2. Once you have "Copied" the data into JMP, what is the Data Type of the column that has the "03.10.14" data in it?
  3. Assuming the "03.10.14" data has a Data Type of Character, what are the steps you are using in the new column to convert it to a Numeric data type?

I suspect your issue with the "20.10.15" values, is that somehow JMP thinks the first values in the string is the month value, and not the day value.

Jim
Highlighted
Marc3
Level II

Re: change date format

Hi Jim,

 I never though I could open an xlsx file directly in JMP, I tried and I was not able to open it. 

The "date" column is a character / ordinal. In the new column I changed it into numeric / ordinal and formated into yyyy-mm-dd. The change into numeric resulted in the numeric value, which I then formated into yyyy-mm-dd.  

What do I have to do to change JMP thinking it is a string = month value?

Thanks for your support, 

Marc

Highlighted
txnelson
Super User

Re: change date format

Here is a simple way to do what you need that will insure that the proper parts of you inputted column are selected for the day, month and year.

Input your data

Create a new numeric column, and specify in the Col Info to use the Date format that you want

Use the following formula in the column to convert the data from your original column into a JMP numeric date column.

Date DMY(
	Num( Left( Char( :yourcolname ), 2 ) ),
	Num( Substr( Char( :yourcolname ), 4, 2 ) ),
	Num( "20" || Right( Char( :yourcolname ), 2 ) )
)

 

Jim
Highlighted
Marc3
Level II

Re: change date format

Hi Jim, 

 It worked! Thank you! The only thing I had to do is remove DMY from the formula! 

Awesome.

Thank you again,

Marc

Highlighted
Marc3
Level II

Re: change date format

Jim,
one more minor but important point, is it possible that when using data type "char" that I can not get a numeric output date like yyyy-mm-dd? I tried to establish another column to change from the new column with a char date to a output as mention above but I was not able to create it.
Please let me know if you have an additional solution!
Thank you,
Marc
Highlighted
txnelson
Super User

Re: change date format

I am positive that such a character date structure can be output.  I have a couple of questions.

  1. What is the original column's format?
    1. Numeric/Character
    2. If Character, what is it's structure, 20JUL2019 or 07/20/2019, or what?
  2. Why do you need the new column to be a character column?
Jim
Highlighted
Marc3
Level II

Re: change date format

1. orignal date column: charater, ordinal

2. dd.mm.yyyy

Without looking further into change the character column, I believe the formula formated it automatically to character, because I can not change into another data type / modeling type without having an empty, new column again. 

 

Highlighted
Marc3
Level II

Re: change date format

Screen Shot 2019-09-24 at 8.58.54 PM.pngScreen Shot 2019-09-24 at 8.59.58 PM.png

Highlighted
txnelson
Super User

Re: change date format

Your reponses and question appear to me that you are not understanding how JMP handles date values.  JMP handles dates and times, using a numeric value which is equal to the number of seconds since Midnight, January 1st, 1904.  The way that JMP converts this into one of the displays of 09Feb2015, or 02/09/2015, etc. is to set a JMP format on the column that has the numeric values.

Therefore, if you have a character column, called "Date", with the values like "25.04.15", and you want to convert that into a JMP Date value, you can create a new column and if you apply the following formula, 

Date DMY(
			Num( Left( Char( :Date ), 2 ) ),
			Num( Substr( Char( :Date ), 4, 2 ) ),
			Num( "20" || Right( Char( :Date ), 2 ) )
		)

it will take the character value of "25.04.15" and convert it into 3512764800.  This is the number of seconds from Midnight January 1st, 1904 until Midnight, April, 25th, 2015.  Now what is missing, is that you don't want the data displayed in the number of seconds.  What you want, is for the value to be displayed in the data table, and on your reports in 2015-04-25.  To do this, you just set the format for the column to "yyyy-mm-dd".  The advantage of storing dates in this numeric form is that one can then order the data by date, and can do addition and subtraction on the values, or even calculate the mean date etc.

 

Below is a little script that when you run it, it will create a data table with a single character column that has the structure of dd.mm.yy.  The script then creates a numeric JMP Date column changing the display structure to yyyy-mm-dd.   The stript then goes on and creates a character column, which manipulates the character Date columns value into a new character string of the structure "yyy-mm-dd".

Names Default To Here( 1 );

// Create a beginning data table
dt = New Table( "Dates",
	New Column( "Date",
		Character,
		ordinal,
		Values( {"25.04.15", "22.09.15", "03.11.15"} )
	)
);

// Create a new numeric column that contains a JMP date value
// and tell JMP to format the numeric value using a
// yyy-mm-dd structure
dt << New Column( "Numeric Date",
	formula(
		Date DMY(
			Num( Left( Char( :Date ), 2 ) ),
			Num( Substr( Char( :Date ), 4, 2 ) ),
			Num( "20" || Right( Char( :Date ), 2 ) )
		)
	),
	format("yyyy-mm-dd")
);

// Create a new character column that rearanges the data from
// from the character column "Date"
dt << New Column( "Character Date",
	character,
	formula(
		"20" || Right( Char( :Date ), 2 ) || "-" ||
		Substr( Char( :Date ), 4, 2 ) || "-" || Left( Char( :Date ), 2 )
	)
);
Jim
Article Labels

    There are no labels assigned to this post.