change date format

[#1]

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]

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

[#3 · Accepted solution]

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.

[#4 · Accepted solution]

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