Just seen peter webbs vid of automatically closing and greening up (redding in the shown example) before the off in excel.
The formula simply states if last updated time is greater than the event start time green up. Been messing about trying a few things lately and greening at a pre determined time would be a useful safety mechanism.
If the race card as been delayed by say 10 minutes, how would you write the formula to green up after say 9 minutes 30 seconds i.e. 30 seconds before the new scheduled off time? Or indeed if you wanted to exit and green up 55 seconds after the advertised time.
I’m ok with excel commands such as if, and, not, nor etc, but useless with time and dates. I don’t understand them at all (the way they are handled. /manipulated in excel).
Any help/advice would appreciated.
auto greening at the off
Hi,
In my own spreadsheets that green-up at various times I convert the countdown time (Bet Angel cell F4) to an Integer that will count down in seconds (e.g. 10 mins before the official start time = 600). You can do this manually by referring to the countdown time cell (F4) using the Hour, Minute and Second functions in Excel (you'll probably just want to use 'Minute' and 'second').
For example use the formula '=Minute(F4)'
This will give you the official number of minutes before the 'off' that you choose. You then refer to this number in the 'green-up' cell L6.
If you want to start your count after the official countdown gets to 0 (i.e. green-up after the official start time) you can use Excel VBA to set a variable on reaching 0 and then using 'Application.ontime' to set a green-up bit after a time delay you choose. This is a bit more complex because you need to have at least some understanding of VBA. (You can do this using Excel functions as well but it gets quite long winded so I won't go into it here)
If you want to automate the time you've set for every successive race at a certain track you can conduct a 'Left' function search on Cell A1 to extract the track name and use the result as a condition in the green-up cell L6 (i.e. If left(A1,4)="Wind" wait additional 10 mins)
It's possible to entirely automate what you are doing in this way so you don't have to keep remembering to delay in any particular race.
Hope this helps.
In my own spreadsheets that green-up at various times I convert the countdown time (Bet Angel cell F4) to an Integer that will count down in seconds (e.g. 10 mins before the official start time = 600). You can do this manually by referring to the countdown time cell (F4) using the Hour, Minute and Second functions in Excel (you'll probably just want to use 'Minute' and 'second').
For example use the formula '=Minute(F4)'
This will give you the official number of minutes before the 'off' that you choose. You then refer to this number in the 'green-up' cell L6.
If you want to start your count after the official countdown gets to 0 (i.e. green-up after the official start time) you can use Excel VBA to set a variable on reaching 0 and then using 'Application.ontime' to set a green-up bit after a time delay you choose. This is a bit more complex because you need to have at least some understanding of VBA. (You can do this using Excel functions as well but it gets quite long winded so I won't go into it here)
If you want to automate the time you've set for every successive race at a certain track you can conduct a 'Left' function search on Cell A1 to extract the track name and use the result as a condition in the green-up cell L6 (i.e. If left(A1,4)="Wind" wait additional 10 mins)
It's possible to entirely automate what you are doing in this way so you don't have to keep remembering to delay in any particular race.
Hope this helps.
Cheers willooo,
I will give your method a go probably tomorrow/Friday, but not the vba.
I have come up with a different way than you. I had a vague memory that excel stores and manipulates date/time as a decimal number (confirmed it with a google search).
I copied the event start time, formatted as a decimal. Entered the time I wish to delay by in hh,mm,ss format i.e. 00:01:30. In a different cell. Copied to a new cell and formatted that as a decimal.
Then in another cell added the 2 decimal numbers and formatted the result as time in hh,mm,ss. resulting in a new time display 1 min 30 secs later than the event star time,
For example Redcar event start time 14:00:00, now have a new event start time of 14:01:30.
If you subtract the 2 decimals and format the result as hh,mm,ss , in the above Redcar 14:00:00 you get a new event start time of 13:58:30.
Using Peter’s formula to green up---if last updated time is greater than event start time(substitute that cell for the one displaying the new one), green up.
Using the above I can now green/.red at pre determined times after the official off, or before the official off.
I like your method of altering the off time at a specific track, and will look into it when I get a bit more time. Again will try your methods soon when I get a bit more time. They seem more “elegant” than mine.
Thanks for reply really appreciated.
Regards tony.
I will give your method a go probably tomorrow/Friday, but not the vba.
I have come up with a different way than you. I had a vague memory that excel stores and manipulates date/time as a decimal number (confirmed it with a google search).
I copied the event start time, formatted as a decimal. Entered the time I wish to delay by in hh,mm,ss format i.e. 00:01:30. In a different cell. Copied to a new cell and formatted that as a decimal.
Then in another cell added the 2 decimal numbers and formatted the result as time in hh,mm,ss. resulting in a new time display 1 min 30 secs later than the event star time,
For example Redcar event start time 14:00:00, now have a new event start time of 14:01:30.
If you subtract the 2 decimals and format the result as hh,mm,ss , in the above Redcar 14:00:00 you get a new event start time of 13:58:30.
Using Peter’s formula to green up---if last updated time is greater than event start time(substitute that cell for the one displaying the new one), green up.
Using the above I can now green/.red at pre determined times after the official off, or before the official off.
I like your method of altering the off time at a specific track, and will look into it when I get a bit more time. Again will try your methods soon when I get a bit more time. They seem more “elegant” than mine.
Thanks for reply really appreciated.
Regards tony.