Formatting Asian dates to short dates

cchueng

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.

dataTable.jmp

Craige_Hales

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.