Keep getting error in status bar please help.

[Willygubbins]

Hi i have be trying to place a bet relative to the start time of a race by referencing the countdown timer cell F4 the formula as follows: =IF(F4=00:03:00,"BACK","")
I have also tried: =IF(F4<00:03:00,"BACK","")
But i always puts an "ERROR" in the status field.... what am i doing wrong?
Odds cell has =G9 for example.
Any help gratefully accepted.
Thanks in advance.

[ShaunWhite]

You need to put some quotes around the time.

00:03:00 isn't a number, it's a string.

Try IF(F4<"00:03:00","BACK","") ... You might get on even better testing the numerical represntation of the time.
"00:03:00" is just the way it's formatted and displayed, not the underlying value in the cell. Much in the same way as you'd test for <1001 if the cell contained "£1,001.00".

Times and dates have been a weak point in Excel since about .....1987.

[ShaunWhite]

If the time field F4 is a string rather than a number, you could also try converting it into seconds first with....

F999 = (value(mid(f4,1,2)) * 3600) + (value(mid(f4,4,2)) * 60) +value(mid(f4,7,2)) and the test f999 against 180, at least you'll be able to eyeball what's going on and see if you get a sensible number in F999 ?

[nigelk]

I only trade the last 10mins pre-off, never in play, and don't worry about the seconds.

Gives the number of mins before the off.

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

[ShaunWhite]

I don't worry about the seconds either nigel, I just prefer to use the same standard for time calcs everywhere so I can share my macros around different sheets. I just like to use the one unit for time, not minutes here, hours there etc.
I'm a creature of habit and this is a 30 year one. To coin a tired old phrase...It's horses for courses.

btw, your code should read ...
=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)))

You had the second part as 5,2 ) so were only deducting the second digit of the minute.

[ShaunWhite]

As a general suggestion, try and avoid the use of excel references like 'F4'.

If you used named ranges you'll save yourself a whole world of pain should you ever need to move things around (more a macro issue than a worksheet one but they can get screwed up too). It also makes your code (or cell functions) much easier to read and maintain.

It seems like a chore but it only takes seconds and you'll be glad you did.

[nigelk]

btw, your code should read ...
=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)))

Don't forget the minus figure that appears at beginning of the timer once the start time has been exceeded. You have to change the mid formula to take account of this.

Cheers,Nigel

[ShaunWhite]

btw, your code should read ...
=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)))

Don't forget the minus figure that appears at beginning of the timer once the start time has been exceeded. You have to change the mid formula to take account of this.

Cheers,Nigel

They probably both should to be 5,2 then .... either way I think we both know what we're doing...but it would be nice to here from the OP to see if he's got it working.

[Willygubbins]

You need to put some quotes around the time.

00:03:00 isn't a number, it's a string.

Try IF(F4<"00:03:00","BACK","") ... You might get on even better testing the numerical represntation of the time.
"00:03:00" is just the way it's formatted and displayed, not the underlying value in the cell. Much in the same way as you'd test for <1001 if the cell contained "£1,001.00".

Times and dates have been a weak point in Excel since about .....1987.

Hi Shaun this didn't work, it fired a bet immediately when the timer read 02:16:34.......

[Willygubbins]

If the time field F4 is a string rather than a number, you could also try converting it into seconds first with....

F999 = (value(mid(f4,1,2)) * 3600) + (value(mid(f4,4,2)) * 60) +value(mid(f4,7,2)) and the test f999 against 180, at least you'll be able to eyeball what's going on and see if you get a sensible number in F999 ?

This just came up with 00:00:00 in the f999 cell.

[Willygubbins]

I only trade the last 10mins pre-off, never in play, and don't worry about the seconds.

Gives the number of mins before the off.

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

Hi Nigel, where should i use this formula?

[nigelk]

Anywhere on the Bet Angel sheet. If you need to put it in a different sheet, just make sure the sheet with thecountdown timer on it is referenced, on another sheet it would be: =IFERROR(VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),5,2)))

I should point out that the second part of the formula just adds a minus figure to the minutes if the start time has been exceeded.

[Willygubbins]

Anywhere on the Bet Angel sheet. If you need to put it in a different sheet, just make sure the sheet with thecountdown timer on it is referenced, on another sheet it would be: =IFERROR(VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),5,2)))

I should point out that the second part of the formula just adds a minus figure to the minutes if the start time has been exceeded.

I pasted this in to a randon cell on the bet angel sheet and it just read as the formula and did not display any minutes?

[Willygubbins]

Scratch that i got it working, Thanks Nigel

[ShaunWhite]

Anywhere on the Bet Angel sheet. If you need to put it in a different sheet, just make sure the sheet with thecountdown timer on it is referenced, on another sheet it would be: =IFERROR(VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),5,2)))

I should point out that the second part of the formula just adds a minus figure to the minutes if the start time has been exceeded.

I pasted this in to a randon cell on the bet angel sheet and it just read as the formula and did not display any minutes?

OK try building the formula step by step, it will help you to see where the issue is and also teach you what the functions do.

Use something like these steps below and see where it stops working. If a machine doesn't work you need to check each cog until you find the broken one, excel is the same.

='Bet Angel'!F4
=TEXT('Bet Angel'!F4,"hh:mm:ss")
=MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),4,2)
=VALUE(MID(TEXT('Bet Angel'!F4,"hh:mm:ss"),4,2))

At what point does the cell stop showing what you expect to see?