JMP 17 not using System Locale for Date Format

[#1]

Jmp version: JMP 17.2.0

I have set in Windows Specific settings for JMP to use the system locale (checked and defintely UK). Restarting software or PC does not result in that locale choice being respected when date-formatted data is imported from Excel.

The data is formatted in Excel as custom:dd/mm/yyyy hh:mm, but upon importing it defaults to m/d/y. If I change the format in excel to be even more explicit and use date:dd/mm/yyyy then it still imports as m/d/y.

I have seen vaguely similar posts in the past asking for default datetime formatting options, which JMP 16 supposedly addressed in part by respecting Excel date formats. I skipped straight to JMP 17 from JMP 15 so not sure if it worked correctly there, but it certainly doens't seem to work in JMP 17 (or at least my installation).

I would be very grateful if someone could please point out what might be the issue here. 

[#2]

I probably don't have the answer, but these may help answer the question:

Are you importing from a .xlxs file or from a .csv file that Excel exported?

Win 10 or Win 11?

Can you add a tiny file that demonstrates the problem?

[#3]

Hi Craige,

Apologies for the lateness, turns out Outlook throught the JMP emails were junk! 

I am using Windows 10, and the files were indeed CSV. I have attached a small example file as requested (in CSV and XLSX). I have tried with both formats and the result is the same. Screenshot below is an example using the excel plugin to create a data table from the XLSX file, and the resultant datetime field using the incorrect format.

I have now tried importing the file by opening in JMP and selecting to use the set preferences, and that works in setting the correct format.

If I create a new empty table and copy/paste the table from the open CSV file, then that also works by using the import preferences.

So I think the problem may be in the Excel plugin itself? There are very few options in the Excel plugin unfortunately:

Thanks for taking the time to look at this. I suspect that I will just have to avoid using the plugin until this is resolved.

Callum

[#4 · Accepted solution]

JMP tables have two formats, one for the way numbers are typed into a cell and one for the way numbers are displayed. If the m/d/y and d/m/y formats get used together, it will be hard to puzzle out.

Someone else, or tech support, will have to help with the plugin.

JMP can use Open(...) for either a .xlxs or a .csv file. They are very different; I'm most familiar with the .csv.  .xlsx has the advantage of not needing to export the .csv from excel first. Someone else, or tech support, can help with the .xlsx case.

JMP has two ways to open a .csv, either using open(...) or using Multiple File Import. MFI is fast and has fewer options and makes no guesses. Open(...) has options, and the guessing can make it slower. Both MFI and open can be scripted or used from the menu. (As I recall, MFI will import the dates as character and you can convert them after the import.)

The file->open support for .csv files will try to guess the m/d/y vs d/m/y format but will always guess m/d/y if there are no days bigger than 12 (your example data only has days <= 12.) The CSV wizard will let you specify a format, and you can look at an imported table's source script to see how it works. The guessing behavior is only useful for one-off imports. An explicit format should be used in a production script, not the guess, and not a preference.

Open(
	"$DESKTOP/date.csv",
	columns(
		New Column( "date",
			Numeric,
			"Continuous",
			Format( "d/m/y", 10 ),
			Input Format( "d/m/y" )
		)
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		First Named Column( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

Desktop\date.csv like this has an ambiguous date which the script above will interpret as d/m/y for both input and display.

date
2/3/2000

The resulting table looks like

Also showing the column info dialog.

Here's how the import wizard chooses the date format

[#5]

Hi All, this date format issue is an ongoing nuisance. I'm on JMP v18 and it's certainly still not resolved. The worst is, that you can't even force the d/m/y format upon JMP. 

Check out the attached screenshots: import from csv, date format was dd/mm/yyyy. 

When I go to column info and set input format to d/m/y (red square), it changes 4/11 (4th Nov) to 11Apr. If I set input format to m//d/y (yellow square), it ALSO!!! changes to 11Apr.

JMP cannot accept d/m/y, it seems, unless the day/date is beyond the 12th. Then even JMP backs down sullenly.

[#6]

Once the data is in the data table as a date-time value (a numeric value in seconds since 1904), it can't be fixed/changed by changing the numeric input format on the data table. Future values hand-entered into the table will respect the current input format.

The table's output (display) format can be changed at any time; that does not change the internal representation, just the way the values are shown.

The right way:

The input format, specified in the CSV import, must be correct when the CSV file is read and converted to a datatable. It can't be fixed later; the 4/11 vs 11/4 error is already in the numeric seconds since 1904 value.

[#7]

Please (!!) support this wish:
CSV import force MDY or DMY date format 

 One of the most kudoed "new" wishes.

@Sarah-Sylvestre . is the status information up to date?