Using times as a variable

Question

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

unknown · Answer

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.

richardinoz · Answer

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

richardinoz · Answer

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