Time values in xls or xlsx, csv, and JMP

[#1]

Hi all, I have a strange issue with the time values from my excel. I have some data (example is attached here as excel file), the time column is an interest for me and I would add the date and time column together to create a date time column. To analyze the data, I use JMP so I am importing the excel file.

However, I ran into a number of oddities: 

1. When I import multiple files (from customers to analyze their data for them), the time values the time values (in seconds) are negative and it is starting from 1899 when I changed the format to mm:dd:yyyy hh:mm:ss. When I save the file as CSV again, then open again with JMP, the time values are correct (as listed on the original excel file).

2. When I change the format of the excel files to CSV, the time values are correct once imported into JMP. For the files that are xls or xlsx, I will need to change them into CSV. Why is that? 

Below are notes of my thoughts on what is going on and how I arrived at the two solutions that result in a desired date-time format.

As you’ll see in the “Terrible Formula” column, I had to do a couple things as work arounds:

• Covert the date and time into character strings, then concatenate both columns. Copy the properties, paste into a new column and then change the format back to numeric. But this does not always work!
  • The Second() function extracted negative values…and only Second(), which was interesting.  the formula is below 
  • Informat(
    Char( Format( :Date, "mm/dd/yyyy" ) ) || " " ||
    Char( Hour( :Time, 24 ) ) || ":" || Char( Minute( :Time ) ) ||
    ":" || Char( 60 + Second( :Time ) ),
    "mm/dd/yyyy HH:mm:ss"
    )

However, both solutions seems a little laborious (besides saving the excels imported into JMP as CSV, then open with JMP again). Is there another way to correct the time values faster? Automated? while importing the excel files into JMP?

[#2]

Correction:

Below are notes of my thoughts on what is going on and how I arrived at the two solutions that result in a desired date-time format.

1. Covert the date and time into character strings, then concatenate both columns. Copy the properties, paste into a new column and then change the format back to numeric. But this does not always work!

2. The Second() function extracted negative values…and only Second(), which was interesting.  the formula is below 

Informat(
Char( Format( :Date, "mm/dd/yyyy" ) ) || " " ||
Char( Hour( :Time, 24 ) ) || ":" || Char( Minute( :Time ) ) ||
":" || Char( 60 + Second( :Time ) ),
"mm/dd/yyyy HH:mm:ss"
)

---

[#3 · Accepted solution]

Are you sure the Excel file you attached is the correct one?  The workbook contains files with information about internal meetings.

[#4]

Please ignore the one attached in the original post and use this as example. Thank you for catching that.

[#5 · Accepted solution]

I opened the Excel file directly into JMP,  I used the wizard to indicate the data heading was on row 6 and the data started on row 7.  It generated the following JSL

Open(
	"$DOCUMENTS/Discussion Group/Time_issue_7Sep (2).xlsx",
	Worksheets( "W-A20122_Halexistar_20210820-12" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 6 ),
		Data Starts on Row( 7 ),
		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( 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( "-" ),
		Column Numeric Format(
			Index( 2 ),
			Column Name( Variable 1 ),
			Format( "Best", 9 )
		),
		Column Numeric Format(
			Index( 3 ),
			Column Name( Variable 2 ),
			Format( "Best", 9 )
		)
	)
)

Both the Date and Time fields are read in as correct Date and Time JMP values.  Then all I had to do was to run

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row( :Date = :Date + :time );

Import multiple files does not seem to be able to handle the row offsets to properly read in the headers from row 6 and data starting on row 7;

I suggest you create your own JSL loop to read in each of the files using the above input code.

[#6]

That is very interested. I opened with the wizard but the source's script is different than yours. 

Open(
    "C:\Users\liew-6\OneDrive - Mettler Toledo LLC\Desktop\JMP\Time_issue_7Sep.xlsx",
    Worksheets( "W-A20122_Halexistar_20210820-12" ),
    Use for all sheets( 1 ),
    Concatenate Worksheets( 0 ),
    Create Concatenation Column( 0 ),
    Worksheet Settings(
        1,
        Has Column Headers( 1 ),
        Number of Rows in Headers( 1 ),
        Headers Start on Row( 6 ),
        Data Starts on Row( 7 ),
        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( 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( "-" )
    )
)

And if I changed the format of my time to 'best', it is shown negative values. I have attached it here.

[#7]

In terms of the multiple excels import, I am using a script shared by someone a while ago. Attached here.

[#8]

I have replicated your findings.  JMP 13-16 all read in the Excel values with the same negative values.  I looked into the Excel spreadsheet an could not find any reason for the negative values, but then again, I am not an Excel expert.

Maybe another member has an Idea?

[#9]

If you are seeing some negative values for a duration, or a date value imported as a double into JMP and reconverted to a time,  it may be because Excel and JMP use different reference dates for their state of date/time values.  Excel uses 1/1/1900, and JMP uses 1/1/1904.  There is some information on a Community post here:

https://community.jmp.com/t5/JMPer-Cable/Using-dates-times-datetimes-and-durations-in-JMP/ba-p/68689

Brian Corcoran

JMP Development