Find correct time difference in pasted data

[johnmeyer]

My daughter ran in a big running race this weekend and I want to take the split information from the HTML page and then calculate the pace for each mile by taking the difference between columns. This is obviously very simple stuff, but there is a problem (which I describe below). Here's the table I am starting with:

http://www.usatf.org/events/2012/OlympicTrials-Marathon/results/WomenSplit.asp

I can copy/paste the data into Excel with no problem. However, I can't figure out how to get Excel to treat the times that are under one hour (e.g., 48:03, which means 48 minutes and 3 seconds) in the same way as the times that are over one hour (e.g., 1:23:17). The actual decimal time value that results from the copy/paste operation is completely different for the times greater than one hour than those which are less than one hour. This screws up the difference calculations at the point for each runner where her time crosses the one hour mark.

This has got to be easy, but I've searched this forum and spent a lot of time trying to figure out how to get Excel to treat the pasted information in a way that is consistent.

Can anyone give me a hint on how to handle what should be a very simple problem?

Thanks!

 

[sheetspread]

Format Cells, Custom, [h]:mm:ss

 

[johnmeyer]

Format Cells, Custom, [h]:mm:ss

Thanks, but ...

I just tried this, and it doesn't work. I still get the discontinuities when the time goes from less than one hour to more than one hour.

 

[sheetspread]

That only happens with the first >1 hr column, but I'll find a way to fix it. Excel adds extra :00s to the <1 hr values so we might just have to remove them.

 

[AlphaFrog]

I don't know if there is some trick way to correctly import the data, but here's a formula that will correct the times after they are imported

If the time value is greater than 4 hours, the formula will convert the hours as minutes and the minutes as seconds. If the time value is less than 4 hours, it doesn't convert it. This should work for your data as all the time values that need correcting are seen as 6 hours or larger. The time values that don't need correcting are less than 3 hours.

=IF(A2>4/24,TIME(0,INT(A2*24),MINUTE(A2)),A2)

Format the cell with this formula as hh:mm:ss

 

[johnmeyer]

=IF(A2>4/24,TIME(0,INT(A2*24),MINUTE(A2)),A2)

Format the cell with this formula as hh:mm:ss

That works.

It took a couple of minutes to create a new data set using your formulas followed by copy/paste special. No big deal; just straightforward spreadsheet mechanics.

If I had to do this on dozens of data sets, I would want a simpler solution that didn't require creating an intermediate data set. However, your solution worked peerfectly and I now have the pace times for this one race, and that's all I need.

Thanks!!