BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Community Member

Hi, everybody

I am a new user of JMP.

When I save JMP file to Excel file, format of file is changed. For example, "09-1903" to "Sep-03" and "10-1915" to "Oct-15".

How can I maintain original value, preventing change to date format ?


0 Kudos

Re: Hi, everybody

Hi ssailorr,

in the documentation it is stated that the export to Excel will not keep all formats and settings:

"Data table cell colors are maintained when you export a data table as a Microsoft Excel .xlsx file. Hidden data and excluded data are also included. Most formatting and formulas are not exported to Excel Format."

One of the reasons for your case probably is that both JMP and Excel are able to recognize your date Format as a date, but do not have this format as a display Format, at least for my versions.

There might be a chance to use "Save Database" command connecting to Excel Files instead of a database. But I haven't tried this so far. And honestly I do not think it will Keep the Format either (based on my comment above).

Not much of help, but at least an explanation.

Cheers, Martin

Community Trekker

Re: Hi, everybody

This isn't a very elegant solution, but it might work for your situation. To explicitly tell Excel that the value is text (not a numeric date value), you can place a single quote (tick mark) before the text. For example, " '09-1903 " (without the double quotes, of course).

It's possible to add this single quote using formulas in JMP. If the column in JMP is numeric, then you can create a new column in JMP with the following formula:

"'" || Format(:numeric column)

That's a single quote inside two double quotes, 2 vertical bars (concatenate function symbol), and the Format function (translates the date variable to its formatted form as a text/character value).

If the column in JMP is character, then you can create a new column in JMP with the following formula:

"'" || :character column

Once again, that's a single quote inside two double quotes, and 2 vertical bars (concatenate function symbol).

In Excel you won't see the single quote in the cell, but it's still there, so it might create some issues if you do any Excel calculations based on that cell.

Best of luck.