Time triggers

[kwame]

I am happy (and grateful) for Castillo's (D2-int(D2))*24 and the follow on IF statement, but they are spoiled by the fact that the server I connect through runs about 3 minutes fast, and I obviously cannot adjust it.

What that means is that a race starts when D2 reaches minus 00.03.00.

Once D2 has passed 00.00.00, this causes Castillo's expression to produce a #VALUE# error.

Can someone please tell me how I can add 3 minutes to the D2 time (say in X2), before applying Castillo's expression to that reference cell?

[phrenetic]

The difficulty is that when the value is positive it is treated as a number by Excel but when it is negative, it is treated as a string.

To get round this, you could try the following:

Code: Select all

IF(LEN(D2)=9,-1*(VALUE(MID(D2,2,2))+VALUE(MID(D2,5,2))/60+VALUE(MID(D2,8,2))/3600),(D2-INT(D2))*24)

This would mean that 00:03:00 would be 0.05 and -00:03:00 would be -0.05.

To be honest, I'm not sure why the positive entry can't just be D2*24 - can anyone explain this?

Alistair

[phrenetic]

Actually, best to amend this to:

Code: Select all

IF(LEFT(D2,1)="-",-1*(VALUE(MID(D2,2,2))+VALUE(MID(D2,5,2))/60+VALUE(MID(D2,8,2))/3600),(D2-INT(D2))*24)

as some positive time values appear to have a length of 9 (and therefore cause the wrong part of the formula to calculate).

Now the formula specifically looks for a negative value.

Alistair

[kwame]

Thanks very much Phrenetic.

I test drove the first version this afternoon and it solved my problem, except it fired prematurely on a few occasions. This may have been the instances that caused you to offer the second version.

I'll try that at Wolverhampton later.

Cheers,

Kwame

[knot]

IF(LEFT(D2,1)="-",-1*(VALUE(MID(D2,2,2))+VALUE(MID(D2,5,2))/60+VALUE(MID(D2,8,2))/3600),(D2-INT(D2))*24)

i cant get this to work .

[phrenetic]

knot

What error did you get?

This is just a standard formula - forgive the stupid question, but you did precede it with an = sign, yes?

Alistair

[knot]

= was used. When the countdown clock reached zero your formula turned to #### never went - 00:00:01

[kwame]

Version 2 worked fine for me (thanks Phrenetic), and cut out the premature bet-placement.

One other query (I can't find an answer elsewhere in the forum): what do I have to do to make the programme move to the next race on my quickpick list as soon as a bet has been placed? I am using the Beta version.

[phrenetic]

knot

#### would tend to suggest that the cell width was not wide enough or the cell formatting was incorrect. Try widening the cell and setting the format to General.

Alistair