Help with negative time values please

[Chisp]

Hoping someone can help. Currently in cell D2 in excel it displays the time countdown to the off time. I want to create a trigger that will cancel all bets when it is 30 secs past the off time (currently this displays a negative value in D2 of -00:00:30).

However, try as I might, I cannot create a formula to work. Can someone please point me in the right direction.

Thanks in anticipation.

[Smokin Joe]

I'm struggling with that one.

I have triggers based on the time value in Cell D2, and my formula work, but it seems to deal with jus positive numbers. It appears that when D2 becomes negative Excel doesn't recognise it as a number.

FWIW, the formula that I would use is:
=IF(D47<(Number of Minutes pre Off/1440),"CANCEL","DO SOMETHING ELSE")

But as I said that doesn't work for negative numbers.

[Smokin Joe]

For D47, read D2 above.

[dgs2001]

Have a look at this thread

http://www.gruss-software.co.uk/forum/v ... ight=timer

Also see this thread

http://www.gruss-software.co.uk/forum/v ... ight=timer

Duncan

[Roger]

I'm not sure, because I can't test it live, but I think this should work:

=IF(LEFT(D2)="-",IF(VALUE(TIME(MID(D2,2,2),MID(D2,5,2),RIGHT(D2,2)))>VALUE("00:00:30"),"BACK",""),"")

[Roger]

Ooops, sorry, should be:

=IF(LEFT(D2)="-",IF(VALUE(TIME(MID(D2,2,2),MID(D2,5,2),RIGHT(D2,2)))>VALUE("00:00:30"),"CANCEL",""),"")

[Chisp]

Many thanks for the suggestions. Will give them a try and let you know howI get on.

Much Appreciated.

[Chisp]

Just the ticket Roger. Thanks again.

[Roger]

You're very welcome.