topic Text time to numeric time in Discussions

Text time to numeric time

Sciguy_1 — Wed, 29 May 2019 15:30:02 GMT

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.

Re: Text time to numeric time

txnelson — Wed, 29 May 2019 15:52:12 GMT

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.

Re: Text time to numeric time

Sciguy_1 — Wed, 29 May 2019 20:06:55 GMT

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.

Re: Text time to numeric time

Sciguy_1 — Wed, 29 May 2019 20:18:25 GMT

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

Re: Text time to numeric time

gzmorgan0 — Thu, 30 May 2019 15:34:07 GMT

@Sciguy_1 ,

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

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.

Re: Text time to numeric time

Sciguy_1 — Thu, 30 May 2019 16:08:38 GMT

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!