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?
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?
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.
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 ),
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( "-" )
And the column it's importing to is
Data Type: Numeric
Modeling Type: Continuous
The dates from the Excel sheet are entered as: dd-Mon-yyyy
There are no labels assigned to this post.