Choose Language Hide Translation Bar
Level IV

Replacing Values with Empty Value

I have a script that is based on an imported excel table. I do not have the ability to change anything in the Excel table and have to do all data manipulation and analysis through JMP. The point of the script is to calculate days between dates entered to track project phases. Some of the dates are importing incorrectly as 31Dec1899 based on the way the formula is set up in the Excel sheet and there are also times when a dash is used to denote an not applicable value. I would like to modify these values to be empty/blank with the "." as if nothing had been entered. Is there  a way to do this? I have tried using match, substitute, and an if statement, but nothing seems to work. Is there a way to do this?


Re: Replacing Values with Empty Value

How does this variable come in to a data table? What does the column look like where you have valid dates and this invalid date?

Learn it once, use it forever!
Level IV

Re: Replacing Values with Empty Value

When the date is populating as 31Dec1899 it is coming from blank cells in the corresponding column in the Excel sheet. The ones with the dashes are entered as dashes in the Excel sheet.

Level IV

Re: Replacing Values with Empty Value

Here's the script section I'm using to import the file.

NS = Open("File Path Here",

Worksheets( "PEN Status Log" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(


Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 3 ),

Data Starts on Row( 4 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Replicated Spanned Headers( 0 ),

Suppress Hidden Rows( 0 ),

Suppress Hidden Columns( 0 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 ),

Multiple Series Stack( 0 ),

Import Cell Colors( 1 ),

Limit Column Detect( 0 ),

Column Separator String( "-" )




Level IV

Re: Replacing Values with Empty Value

And the column it's importing to is

Data Type: Numeric

Modeling Type: Continuous

Format: ddMonyyy


The dates from the Excel sheet are entered as: dd-Mon-yyyy


Article Labels

    There are no labels assigned to this post.