Time values in xls or xlsx, csv, and JMP

fliew0410 — Sat, 10 Jun 2023 23:36:38 GMT

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.

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"
)

This post has the correct excel data, posted one with a lot of mistakes earlier but can't edit or remove.

topic Time values in xls or xlsx, csv, and JMP in Discussions

Time values in xls or xlsx, csv, and JMP