csv file: Day.Month.Year

[#1]

In a few countries date is saved as Day.Month.Year.

Unfortunately, Jmp assumes date to be Month.Day.Year - even when regional settings are enabled in the Preferences.
If there are some dates with first value  >12, Jmp will automatically detect it and change to the non-standard version 
This behavior looks quite dangerous - just 

Is there another setting in the preferences where I can specify the date format such that Days and Months don't get intermixed.

[#2]

I think you have to do two things: the OS setting and the JMP setting.

[#3]

Hm, I seem to be doing something wrong.

My system is configured like this:

(TT = DD)

Jmp:

and when I read a file with DD.MM.YYYY,  JMP reads it as MM.DD.YYYY.
Same when entering values in a cell:

m.d.y.mp4

Video Player is loading.

Play Video

Play

Mute

Current Time 0:00

/

Duration 0:08

Loaded: 0%

0:00

Stream Type LIVE

Seek to live, currently behind liveLIVE

Remaining Time -0:08

1x

Playback Rate

Chapters

• Chapters

Descriptions

• descriptions off, selected

Captions

• captions settings, opens captions settings dialog
• captions off, selected

Audio Track

• en (Main), selected

Picture-in-PictureFullscreen

This is a modal window.

Beginning of dialog window. Escape will cancel and close the window.

Text

ColorWhiteBlackRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-Transparent

Background

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyOpaqueSemi-TransparentTransparent

Window

ColorBlackWhiteRedGreenBlueYellowMagentaCyanTransparencyTransparentSemi-TransparentOpaque

Font Size

50%75%100%125%150%175%200%300%400%

Text Edge Style

NoneRaisedDepressedUniformDropshadow

Font Family

Proportional Sans-SerifMonospace Sans-SerifProportional SerifMonospace SerifCasualScriptSmall Caps

Reset restore all settings to the default valuesDone

Close Modal Dialog

End of dialog window.

Close Modal Dialog

This is a modal window. This modal can be closed by pressing the Escape key or activating the close button.

 

[#4]

The column's input and output formats are both MDY; the video shows the behavior I expect. You might want to change both. You probably don't want them opposite of each other, but setting the output format to 31JAN2024 (ddmonyyyy ?) might help explain what you are seeing. I'm not sure how those 3-letter month abbreviations will work for you either, but it does clear up the ambiguity.

The OS's regional settings, and JMP's Use Regional checkbox, apply to text import (CSV). I don't know if JMP uses the regional settings in reports.

The checkbox was added because there are a lot of CSV files that use DMY and a lot of international users that have the OS set to MDY. It is easier to clear Use Regional to ignore the OS setting than it is to change the OS setting. Often you'll want CSV files processed as DMY, but not always. I think the comma/period/etc for separator/decimal indicator is also part of this discussion. The single checkbox is overloaded; if you need finer control, say to ignore the OS setting for decimal but use the OS setting for date, you'd need to change the OS setting and check Use Regional.

https://en.wikipedia.org/wiki/List_of_date_formats_by_country

https://en.wikipedia.org/wiki/Decimal_separator

[#5]

What do I have to do to directly import D.M.Y without further adjustments.

[#6]

use the CSV import wizard and specify the correct format for the column. (Not MFI, use the file->open->text files and ask for the wizard.) Once you have the script, you can edit it as needed.

[#7]

I would love to have a setting in the preferences which guarantees that none of our new JMP users has to fear that JMP accidentally uses a wrong format.

dt = New Table( "dates",
	Add Rows( 30 ),
	New Column( "YMD",
		Format( "y/m/d" ),
		Formula( In Days( Row() ) )
	),
	New Column( "DMY",
		Format( "d/m/y" ),
		Formula( In Days( Row() ) )
	),
	New Column( "MDY",
		Format( "m/d/y" ),
		Formula( In Days( Row() ) )
	),
	New Column( "DMY2",
		Format( "d/m/y" ),
		Formula( if(row() < 12,In Days( Row() ) ))
	)
);

dt << save("$temp/dates.csv");

dt2 =open("$temp/dates.csv")

[#8 · Accepted solution]

agree. wish: CSV import force MDY or DMY date format 

[#9]

Multiple File Import avoids the issue by importing dates as character so you can change them to the proper format/numeric afterward.