Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Text time to numeric time

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 29, 2019 8:30 AM
(2552 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

5 REPLIES 5

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Text time to numeric time

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Text time to numeric time

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.