Working with time in Excel

[zapbrann]

Hi All,

I am trying to place bets dependant on the time within a football match. I am fairly liberal and can use a 20+ minute window, so that removes the problem of matches starting/running late.

I have started by trying to get a formula to work from cell D2 and just use simple IF statements to say, if the time is less than -01:20:00 (generally 65 mins), but greater than -01:35:00 (~80mins) then place this bet.......

Unfortunately, I can't seem to make it work?

The number in D2 seems to be read as text, and when I take just the number out, because it is negative, excel assumes I am trying to write a formula and forces me to turn it into text again.....

I have had a good rummage around the forum, and various people allude to measuring against time from the off, without actually explaining how to do it!

Any help appreciated.

Regards,

[mak]

this is Gary's

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

it will help you get where you want
if it is below 0 it shows -

[zapbrann]

Mak,

Thanks a lot, this is a great start, and not something I would have got to that quickly!

For those who copy this formula, just beware of how your version of excel treats comma's vs semicolon's, took me a little while to figure out why the formula wasn't working!

Thanks,

Zap.