nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

change date format

Marc3

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.

Accepted answers

txnelson

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.

dateformating.jmp

Marc3

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

All comments

txnelson

I have some questions:

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

Marc3

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

txnelson

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

Marc3

Hi Jim,

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

Awesome.

Thank you again,

Marc