Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
Sciguy_1
Level III

Text time to numeric time

I am having an issue converting a text time format to a numeric time format. Data should be of the format MM:SS.0  (This is the format it was in excel). After the import into JMP I have a long string (i.e. 2.1713e-5) that I seem to be unable to do anything with. This time value represents a time duration that needs to be in the format of MM:SS.0  I have tried a few solutions but nothing quite seems to work. Thank you for your assistance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
gzmorgan0
Super User

Re: Text time to numeric time

@Sciguy_1 ,

Attached is a table with the formula to do the conversion. 


image.png

The format is min:s with 4 decimal places, or if you prefer 1  decimal you will see 0:01.9

 

The internet has many sources regarding conversion of date and time to numeric values. Excel stores time as a fraction of a day.  See link.

When Excel is read into JMP, the numeric stored value is read-in. So to convert that to min:s you need to create a new column that multiplies the read-in value by 86400 and change that formula column's format to min:s 

 

I had to look it up as well. Nice to know.

View solution in original post

5 REPLIES 5
Highlighted
txnelson
Super User

Re: Text time to numeric time

Your specification of 2.1713e-5 indicates that your column is not a character column but a numeric with a format displaying scientific format. If that is the case, you should be able to just go to the Column Info for the column, and change the format to the Time, or Duration format that is appropriate.
Jim
Highlighted
Sciguy_1
Level III

Re: Text time to numeric time

You are correct, if I set my data type to numeric and modeling type to continous I get the value 0.000021713  This is a time duration of 00:01.9 in the format of MM:SS.0  (which is how it was expressed in excel). I have tried creating a new column and modifying formatting but I have been unsuccessful for some reason. The output time format I need is not listed in the choices and not sure if that is part of the issue or if I can somehow create my own format. Thank you for your input. I have alot of time invested in this large data set and I hate to get stuck on time formatting.

Highlighted
Sciguy_1
Level III

Re: Text time to numeric time

I was able to modify the formatting on the column to get the format I desire but it does not populate with my values it leaves it  0:00.0       

Highlighted
gzmorgan0
Super User

Re: Text time to numeric time

@Sciguy_1 ,

Attached is a table with the formula to do the conversion. 


image.png

The format is min:s with 4 decimal places, or if you prefer 1  decimal you will see 0:01.9

 

The internet has many sources regarding conversion of date and time to numeric values. Excel stores time as a fraction of a day.  See link.

When Excel is read into JMP, the numeric stored value is read-in. So to convert that to min:s you need to create a new column that multiplies the read-in value by 86400 and change that formula column's format to min:s 

 

I had to look it up as well. Nice to know.

View solution in original post

Highlighted
Sciguy_1
Level III

Re: Text time to numeric time

Thank you for your effort on this- somehow I missed the fact excel stores in fraction of a day. Thanks again to everyone for your help!
Article Labels

    There are no labels assigned to this post.