Exported data from Adrian Massey

[hughsiepops]

Morning.

I have been using Adrian Massey's site to download and look through some stats. The problem I have is that I am unable to sort via the time format of the races. (Eg. I download them and they appear as 12:40, 1:15, 1:50, 2:25, 2:55, 3:30, 4:05) when I sort on them by time the 1:15 and 1:50 races appear before the 12:40 race.

I would like to be able to convert all the times to the 24 hour clock. I've looked at converting the time using the LEFT_worksheet_function and adding 12 (to make this the 24 hour clock) but I can't get this to work. 1:30 successfully becomes 13:30, but 12:30 becomes 24:30

As far as I am aware I am unable to simply convert the times via the Format > Cells > Number > Time function in excel as they are imported into Excel as plain text.

Would anyone be able to help me with the final piece to my jigsaw please.? I'm trying to use the data available from Adrian Massey's site to back test some ideas I'm thinking of implementing with BA

Thanks
Hughsiepops

[KevinTHFC]

Why not use an IF clause first ie
IF left(time,2) <> 12
then time =left(time,2)+12.

Kev

[Ian]

=TIMEVALUE(A1&" PM") might work if the cell is text. Won't work if any races start before noon though.

[hughsiepops]

Thanks. Just struggling on putting that together.

Here is an example of the exported data. When I import it into Excel I use commas and under the 'ODDS' heading I import it as text

Thanks
Hughsiepops

DATE,TIME,MEETING,SELECTION,ODDS,POSITION,RUNNERS,BF ODDS
02/02/2008,1:25,Doncaster,TIDALBAY,1-4,2,6,1.26
02/02/2008,1:55,Doncaster,CELESTIALHALO,2-5,2,15,1.42
02/02/2008,12:50,Doncaster,KALAHARIKING,6-5,4,16,2.29
02/02/2008,2:25,Doncaster,MOBAASHER,11-4,5,14,4
02/02/2008,3:00,Doncaster,ANDREAS,7-2,7,11,5.1,
02/02/2008,3:30,Doncaster,NYKEL,7-1,F,19,9.2,

[phrenetic]

Hughsiepops

When you have imported your data (with the race time in column B, I assume), just add an additional column and enter the formula:

=B2+TIMEVALUE("12:00:00")

Copy the formula down so that it appears against each occupied row in your spreadsheet.

Make sure the new column is formatted using a custom format of hh:mm and you should have the race times in 24hr format.

Alistair

[Ian]

Alistair, I think that your solution will fail, the same as mine, if the time is before 12 noon.

[Bid]

I'm sure there's a better way of doing it but this will work

=IF(B2<TIME(12,0,0),B2+TIME(12,0,0),B2)

[Ian]

Still doesn't work if the race time is 10 to 12.

[Bid]

10 to midnight no - but it works if it's 10 to midday

[Bid]

Sorry I'm being stupid!

How about?

=IF(B2<TIME(10,0,0),B2+TIME(12,0,0),B2)


Providing there's no races before 10 in the morning or after 10 at night it should work.