Formatting Asian dates to short dates

[#1]

Many companies in Asia type their dates different from that in USA. For example, 3rd Dec. 2021, Asian companies will type it as 03/12/2021 but in USA we type it as 12/03/2021. I have a table which I parse out the dates and convert it into short dates. But the circle ones are wrong, see below image. I used the code below. How do I let JMP know that the dates extracted from the Text column are in this format days_months_year and not months_days_year?

dtlog << New Column( "vStart",Numeric,Continous, Formula(Short Date(Num(LEFT(:Text,8))) )

Attached is the data table.

[#2]

Perform these steps:

1. Right click on your vStart column that has the numeric dates

2. Select "Column Info..."

3. Change format from Best by clicking "Best > Date > d/m/y"

4. Click OK

dtlog:vStart << Format( "d/m/y", 12 );

[#3]

hi mzwald,

How do I merge it with my code?

dtlog << New Column( "vStart",Numeric,Continous, Formula(Short Date(Num(LEFT(:Text,8))) )

[#4]

dtlog << New Column( "vStart", Numeric, Continuous, Formula(Short Date(Num(LEFT(:Text,8)))));
dtlog:vStart << Format( "d/m/y", 12 );

[#5 · Accepted solution]

For the column formula use

Informat( Left( :Text, 8 ), "d/m/y" )

Once num(left(text,8)) is done, it is too late to fix it; num() has already guessed the string is a date and guessed the wrong format, sometimes. Each row is done independently, and num() can't tell it is switching horses mid-stream.

Telling informat the correct format will get you half-way there. The second half is telling the column to use a display format. A reasonable format for checking is one that spells out month names. Here's a complete new column statement:

    New Column( "vStart",
        Numeric, "Nominal",
        Format( "Date Long", 35 ),
        Input Format( "Locale Date" ),
        Formula( Informat( Left( :Text, 8 ), "d/m/y" ) )
    )

looks correct, Nov and Dec line up.

edit: The input format (using the locale date) is silly unless you later remove the formula from the column. The input format is used if you type in a value, but you can't type values in a formula column. You'd probably want the locale's preferred format if you were reentering "Friday, December 3, 2021". The format (using Date Long) controls how the number is formatted for display. The actual value stored, under the covers, is the number of seconds since 1jan1904. That's what the informat function returned.

You probably want continuous, not nominal. Maybe.

[#6]

Thank you, your solution works for me!

[#7]

I got an error :

[#8]

In your original JSL, the column was automatically converted from numeric/continuous (that you requested) to character/nominal (because shortDate() returns character data.) The nominal got picked up in my example after I copied the column's script.

That's a useful message letting you know that the data in a character column does not get formatted. It can't tell you how the column became character.

In your original example, this was in the log window (because shortdate() returns character):

dtlog << New Column( "vStart",Numeric,Continous, Formula(Short Date(Num(LEFT(:Text,8))) ) )
Column "vStart" changed to type Character to match formula.

There was also a hint in the picture you attached: the vStart data was left-justified. JMP uses right-justification for numeric data and left-justification for character data.