cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

Excel date import problem

Françoise
Level V

Good morning,undefinedundefined

 

I can't import the correct dates from an Excel file.

I formatted the Excel date column in the correct format.

I tried several date formats in Jump.

 

Jump does not import all dates and not in the correct format.

 

in yellow: the excel file.

 

does anyone have a solution?

 

Sincerely

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

5 REPLIES 5


Re: problème import date excel

Difficult to say without having the actual files. Perhaps you could put the Excel file here, but only with the first two columns? Also, how are you trying to get the data into JMP? Are you using the Excel wizard? That will allow you to try different things to ensure that you get the data to come in properly.

 

My best guess is that the format of the dates is causing issues. It looks like you have this as dd/mm/yy. However, the first 16 rows or so could easily be interpreted as mm/dd/yy format. JMP is likely picking up that and setting the rest of that column to that format. In JMP, try looking at the column properties for your date column. Make sure that the format and the Input format are both d/m/y. Perhaps that will solve the issue.

Dan Obermiller
Françoise
Level V

Re: excel date import problem

Good morning,

 

Thank you for your prompt response.

I'm using the Excel import wizard and getting the import script to build dashboards.

the problem Excel file belongs to a group of 10 Excel files that have the same function (= analysis calibration file for different materials) . I have no problem with the other 9 files to import the dates. 

I did some column formatting tests for excel and Jmp (I've been using Jmp since version 3).

I keep checking for the excel file....

 

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

Thierry_S
Super User


Re: problème import date excel

Hi Francoise,

It looks like the dates that do not fit the US date format (Month/Day/Year) are ignored because your input is in an international format (Day/Month/Year). As Dan suggested, I recommend using the Excel import wizard to help JMP figure out the date format.

 

Also, when I encounter similar problems, I import the dates as text and then change the column format to Numerical with an Input Format "dd/mm/yyyy" and an output format (Date) of your choosing.

 

Let us know if that helps.

 

Best,

TS

Thierry R. Sornasse
Françoise
Level V

Re: excel date import problem

Hi,

should I do this:

Column( dtMAT, "Date" ) << data type( Character ) << Modeling Type( "Nominal" );
Column( dtMAT, "Date" ) << Set name( "Date origine XLS" );

Col = New Column( "Date" ) << Set data type( Numeric ) << Set Modeling Type( "Continuous" ) << << Input Format( "dd/md/yyyy", 15 ) << Output Format( "dd/mm/yy" ) << Formula( (:Date origine XLS) ); 

JMP imports all dates in nominal character but not possible to have continuous number format

 

cdlt

 

 

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .

Françoise
Level V

Re: excel date import problem

Hi,

 

problem solved with "Informat":

Column( dt, "Date" ) << Data Type( "Numeric", Informat( "d/m/y" ), Format( "d/m/y" ) ) <<
ModelingType("Continuous");

 

Cordially

 

This post originally written in French and has been translated for your convenience. When you reply, it will also be translated back to French .