5 minutes before the off

[Drunkenight]

Hi, Does anyone know what I need to put into my excel spreadsheet to trigger bets to only be placed when there are 5 minutes or less to race time?

I tried =if(d2<5,TRUE, FALSE)

but it didn't work as the time to the race is in the format hh:minmin:secsec
d2 is the cell containing the time to race.

Thanks
Chris

[Ian]

=IF(MINUTE(D2)<5,TRUE,FALSE)

[Drunkenight]

Hi Ian, thanks. I used that and it works, until race time goes negative.

A new problem - The problem is that when the race time goes negative it returns an error message

[GaryRussell]

I use this really long formula which I'm sure could be simpler, but never got round to simplifying it. It converts the time to seconds from start.

Code: Select all

=IF(LEFT(D2)<>"-",(HOUR(D2)*3600)+(MINUTE(D2)*60)+SECOND(D2),-((HOUR(SUBSTITUTE(D2,"-",""))*3600)+(MINUTE(SUBSTITUTE(D2,"-",""))*60)+SECOND(SUBSTITUTE(D2,"-",""))))

[Drunkenight]

Thanks! Works now.