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

 

 

 

 

 

 

12 REPLIES 12
Marc3
Level II

Re: change date format

Thank you for your extended reply.

I worked on the data sheet and converted the "character date" into a numeric date, column "numdate". Afterwards I covert the numberic date into the format I wanted it to be, column "newdate". However, for some reason the new date does not show for some dates stated in the "date" column. Why does it not take certain dates, i.e. 12.11.14 and converts the numdate value into the new format?

I attached a jmp table with the data for you to actually see the data. I hope this could help solving the issue. 

Thanks in advance, Marc

 

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

 

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
Marc3
Level II

Re: change date format

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