Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level I

## Using times as a variable

Is there an easy way to put in times (i.e. 18 minutes 30 seconds) into a data table and have JMP recognize them as numbers rather than categories?  I have an excel spreadsheet with all the data, and would ideally cut and paste it over to JMP.  Plan B would be to just manually enter the data, but I do not know how to work around doing out the math from minutes to seconds for every entry.  Any suggestions?  For those who are interested, my overall goal is to put together a spreadsheet of Cross-Country race times on different courses and use ANOVA to see what courses are faster/slower.

Thanks,

Cam

3 REPLIES 3
Highlighted
Level I

## Re: Using times as a variable

I don't currently have access to JMP but a start might be to convert the times into a standard time notation in Excel, eg 00:18:30. Then when you import the data into JMP it should appear as a numeric variable.

Richard in Oz

Highlighted
Level I

## Re: Using times as a variable

My Excel ('07) does not have a function that replicates scan() in SAS but here is how you could convert text times to numeric values in Excel, assuming the time data is in the rightmost column

• Use Data|Text to columns|Delimited|Space and tell it to skip the columns containing hours, minutes or seconds
• Formula =IF(ISBLANK(D5),TIME(0,B5,C5),TIME(B5,C5,D5))    -- adjust cells to the columns you want
• Times will show as fraction of a 24 hour day
• Copy the time column and paste special values into the next column
• Apply a 24 hour time format (shown as 13:30:55 in the list)
• Delete all intervening columns

Richard in Oz

Highlighted
Level I

## Re: Using times as a variable

If you have a column of character strings ("18 minutes 45 seconds", etc.), you can convert it to numerical values using:

Num( Word( 1, :RaceTimes ) ) * 60 + Num( Word( 4, :RaceTimes ) )

The answer will be expressed in seconds. Choose the h:m:s format so it looks like time values in your spreadsheet.

Article Labels

There are no labels assigned to this post.