Negative time value in Excel

[mcgoo]

Hi All
Newbie BetAngel user..
I have done a bit of forum reading and have seen similar to this come up before but not found a solution that works yet. I have added a macro to the BetAngel spreadsheet that copies data at a certain time to another cell(s)-(using Guardian).This time is determined by the countdown clock in F4.All good at time b4 jump. I am trying to write and IF statement to change the status of the text in the cell (then runs macro to clear cells) if time is negative (after official jump time-Aus racing hardly ever jumps on time and markets often mature after 00:00:00) So far I have tried multiple methods of doing this including setting the 1904 time format in Excel (2013) but no joy.Also e.g. =IF(F4<=(-13*(1/24/3600)),"GO","STOP") and using the Excel TIMEVALUE function
The positive time value works every time with multiple methods as mentioned above, but the negative time either crashes my macro or doesn't change the status of the text in the cell.Has anyone else come across this and if so please can you let me know how you resolved?
Thanks in advance.

[nigelk]

One way is to extract the minutes using the vaue,mid & text functions. (Formula in H5)

When it turns negative, the 2nd part checks for the error and extracts the same info (adjusting the mid parameters).Both of these return a valid number, but I've added a - sign to the error formula to differentiate between 1 min before the off and 1 min after the off.

This has the effect of turning a number into text, so the formula in I5 just checks if it's a number or text. If it's a number, it must be before the off, if text it must be after the off. So your programe can use the I5 value to decide what to do.

[workpeter]

Just use the LEFT function to detect the minus sign. You can use the LEFT function in VBA too.

=IF(LEFT(F4,1)="-","DoSomething","")

[mcgoo]

Thank you very much for those.They look very helpful and should do the trick.I'll give them a go
Cheers

[mcgoo]

Just had a thought-is there a way to check for a specific time value after the off e.g. -15? (this is important for some horse races in Aus)

thanks again

[workpeter]

Depends what you mean after the off.
If the race starts then time value is positive again. So i would use TIMEVALUE and check its in-play.

something like...

If (Sheet1.Cells(4, 6).Value = TimeValue("00:00:15") And Sheet1.Cells(1, 7) = "In-play") Then
'do something
end if

if it hasn't started then i suppose this may work. not tested.

If (Sheet1.Cells(4, 6).Value = "-00:00:15") then
'do something
end if

[mcgoo]

Thanks for that.Yes, the negative seems to stop the TIMEVALUE function from working but that should work
this (needs a bit of expansion yet) seems to be doing the trick though
=IF(AND(LEFT(F4,1)="-", RIGHT(F4,2)="1"), TRUE,FALSE)

Thanks again
**UPDATE***: For those interested,this works:
=IF(AND(LEFT(F4,1)="-", RIGHT(TRIM(F4),2)="15"),"TRUE","FALSE")