JMP doesn't recognize year from date column when importing Excel to JMP table

[#1]

I have an Excel worksheet with date column where the date is shown as D-MMM.

When I imported this worksheet to JMP using Excel Import Wizard, JMP recognize only D-MMM 

After I have edited the column format from <D><-><MMM> to be <D><-><MMM><YYYY>, 

JMP thought that the <YYYY> is 1904 (default year)

What should be the better way to fix this? 

I can only think of two ways currently, not sure if there are any other option:

1. Change the date format in Excel files first (a lot of effort needed, as there are multiple files to be imported)

2. create new formula column

[#2]

Can you show the column properties dialog for Column 1? I'm not sure what I'm seeing. The values are right justified, leading me to believe it is a numeric column (appropriate for date value in JMP), not a left justified character column. But I don't know how to make JMP display a date like that. It might be a custom format, or maybe, somehow, the field width can remove the year?

I'm hoping the year is actually in the data and just not displayed.

edit: read again, ignore above...

It might also help to post a tiny excel file with a single cell with a date that shows the problem.

[#3]

Hi @Craige_Hales 

I have attached my Excel file here, with a bit modification. 

The setting I have used is below. Need to end the import at row 38 else JMP thought that the date column is Character as the Excel file contains some character data below row 38. 

Here below is the pattern that JMP recognize the date column (As Numeric, with format <D><-><MMM> without <YYYY>)

[#4]

I entered a date as you show in your example and formatted the cell in Excel using custom format (m/d/yyyy h:m).  It imported correctly using the Excel import wizard and the JMP add-in for Excel.  The complete format comes into JMP and the Year function correctly identifies the year.

[#5]

Hi @dale_lehman, Thanks for your reply.

I was looking for any other solution rather than changing the excel file itself as the excel files I need to import to JMP are separated into a month of each year (thinking about changing 24 files for 2 years data)

[#6]

Hi @Nita_Natchanok 

One way to do this is to change the date format once you bring the data into JMP. 

1. Import the file as you did using the Excel Wizard

2. Open the column properties for the date column and select "Set Format Pattern"

3. Update the pattern with "<-><YYYY>"

4. Click OK

When I went through these steps, the date was updated to show the year. I changed your data table to include several different years and they all appear to be formatted correctly after changing the output format.

-Scott

[#7]

This works in JMP 17, but not in JMP 16 (looks like a bug was fixed, thanks @Audrey_Shull )

dt=Open(
    "/Z:/2021_01 Date Import Test.XLSX",
    Worksheets( "A1" ),
    Use for all sheets( 1 ),
    Concatenate Worksheets( 0 ),
    Create Concatenation Column( 0 ),
    Worksheet Settings(
        1,
        Has Column Headers( 0 ),
        Number of Rows in Headers( 1 ),
        Headers Start on Row( 1 ),
        Data Starts on Row( 8 ),
        Data Starts on Column( 1 ),
        Data Ends on Row( 32 ),
        Data Ends on Column( 2 ),
        Replicated Spanned Rows( 1 ),
        Replicated Spanned Headers( 0 ),
        Suppress Hidden Rows( 1 ),
        Suppress Hidden Columns( 1 ),
        Suppress Empty Columns( 1 ),
        Treat as Hierarchy( 0 ),
        Multiple Series Stack( 0 ),
        Import Cell Colors( 0 ),
        Limit Column Detect( 0 ),
        Column Separator String( "-" )
    )
);
dt:column1<<format("y/m/d h:m:s");

If you don't have or can't wait for 17, and if the file names contain the date as in the example, you could use the date from the file name. You might be using FilesInDirectory(...) to get the file names already. (There is also a function to get the file's OS date info, but that might have been updated since the file was captured.)

[#8]

Thanks both @Craige_Hales and @scott_allen. 

I have tried it, and of course it couldn't work with JMP 16.2 as you mentioned. 

I'll try it again once I have got the 17 version.