Problem with negative time

[heinz_pt]

Hello, I need to control the starting time of the match to trigger my bets.
The problem is negative time in D2 cell, I can't do anything with it.
Is there any way to transform the cell in a decimal value? I've already tried the option calculation 1904 in excel. Doesn't work, lost a night on this one.

In alternative, can I get the event starting time, via VBA or directly in a Excel cell?

Best regards.

[Shaun]

Do you mean when the time goes to negative any formula you use to control bet time will activate?

If this is the case then this should solve the issue.

Time is in D2
In cell AA1 you have your bet time and in cell AA2 you have (00:00:00)
then your formula is =IF(D2<AA1,1,IF(D2>AA2,1,0))

1 being the flag to bet, this will allow your trigger to continue after the negative number than just use a flag to look for in play.

[heinz_pt]

Doesn't work the return is always 1. No matter the time.

What I want to do is: Place my bet 30 minutes before the start off a match.
For single match works fine.

But if I have 3 matches in row, starting with 20 minutes interval.
If the first match runs long and ends 1 hour later, the 2 match clocks marks -01:00:00, and my trigger can identify the time to trigger the bet for match 2, and the same end match 2 ends and starts match 3.

[osknows]

This is one of Gary's formulae which I expect will be floating around some other posts

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

will convert the time in D2 to seconds before scheduled post as positive and seconds after scheduled post as negative

[Captain Sensible]

Not sure what exactly you're trying to do , do you continue to monitor the market after placing a bet and only move forward once the match is over?

Either way you can strip the start time just by doing a bit of regex on the A1 cell as it comes after the hyphen but if you're only looking for the 30 minute mark you may as well use the D1 cell which is already monitoring the time to the scheduled off.

You have to remember that the time to off in cell D1 is actually being sent as text when the value is negative so something like sticking =TIMEVALUE(REPLACE(D1,1,1,"")) in another cell will convert that into a decimal value. Although if you're looking to avoid betting after the scheduled off that's pretty much pointless and you'd be better off just having your code move on to the next market if the D1 cell contained a minus sign "-".

[heinz_pt]

Not sure what exactly you're trying to do , do you continue to monitor the market after placing a bet and only move forward once the match is over?

Either way you can strip the start time just by doing a bit of regex on the A1 cell as it comes after the hyphen but if you're only looking for the 30 minute mark you may as well use the D1 cell which is already monitoring the time to the scheduled off.

You have to remember that the time to off in cell D1 is actually being sent as text when the value is negative so something like sticking =TIMEVALUE(REPLACE(D1,1,1,"")) in another cell will convert that into a decimal value. Although if you're looking to avoid betting after the scheduled off that's pretty much pointless and you'd be better off just having your code move on to the next market if the D1 cell contained a minus sign "-".

My initial bet is made pre-live, but I'm still continue to monitor the market to execute Stop-Loss or Offset.
I'm going to try both ways to see what works the best.

For the next market, I thinking to use "Market close" of the current to load the next market. My next headache. Most likely to post many more questions where. eheheheheh

My thanks to all have you that have been helping me this days.

[Captain Sensible]

Might be worth you looking at vba for monitoring your markets so you're effectively continually looping thru the markets. Probably be more efficient especially with so many matches all kicking off at the same time.

[heinz_pt]

My idea is to load a QPL with the markets that I want to work. And then, go thru the markets by the order that i put in the QPL.
I believe that's more to do using VBA, my problem is that I have programminng basics, but none in object oriented.
My background is Pascal, C and C++. I now program in Cobol for a living. Is more easy to me to "program"Excel using "IF's", than VBA.

But it some help, I can do it in VBA, I just need to understand how the object's work , and the definiton of the variables.

[heinz_pt]

Used a different approach that worked fine.

IF(LEFT($D$2;1)="-";1;0)