Countdown timer in excel

[Hmesser]

I'm sure this must be an old chestnut, but not been able to come up with a solution despite searching and playing with Excel all afternoon.

I have markets linked into excel and want to trigger when the countdown timer for each market is 1min before start time. Doing an IF formula between specific values of the countdown timer fails as I think excel is converting the timer into decimal behind the scenes.

Any help gratefully received!

[MarkRussell]

Hi,

Put the following formula in a spare cell:-

Code: Select all

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

Basically it converts the time until the off from cell D2 into seconds until the off.
So then you will be able to refer to this in your IF statement, i.e. it when it shows 60 or less

Regards,
Mark

[Hmesser]

That's perfect!

Thank you so much for your help.

[Captain Sensible]

You can use TIMEVALUE to refer to time within excel, like you've noticed excel treats times as numbers but simply displays them as a time in the formatting. TIMEVALUE allows you to input the time and it then gets converted to the decimal value.

The following would trigger between 00:01:00 and 00:00:50

Code: Select all


=IF(AND(D2<=TIMEVALUE("00:01:00"),D2>=TIMEVALUE("00:00:50")),"BET","DON'T BET")