Hi,
Is there an easy way to create an "off time" countdown timer into the Bet Angel excel templates ?
I can see one possible option by extracting the time from the event string in cell A1 using some text functions then subtracting the PC current time or BA "last updated time" from that. However, I am not 100% sure of the uniformity of the A1 value for different sports/events.
If there is a simpler method I would be grateful for any info.
Rgds
Countdown Timer Into Bet Angel Excel Templates ?
Found this solution buried under 'suggestions' you can search and find the entire discussion if you wish: -
First I strip the time from the event field:
=RIGHT(LEFT('Bet Angel'!B1,SEARCH(":",'Bet Angel'!B1,1)+2),5)
Which leaves us with something like "13:40"
Then I subtract the current time which has to be in the same format.
=CONCATENATE(HOUR(NOW()),":",MINUTE(NOW()),":",SECOND(NOW()))
You can put the whole lot in a single cell:
=RIGHT(LEFT('Bet Angel'!B1,SEARCH(":",'Bet Angel'!B1,1)+2),5)-CONCATENATE(HOUR(NOW()),":",MINUTE(NOW()),":",SECOND(NOW()))
You'll need to format the cell as time.
First I strip the time from the event field:
=RIGHT(LEFT('Bet Angel'!B1,SEARCH(":",'Bet Angel'!B1,1)+2),5)
Which leaves us with something like "13:40"
Then I subtract the current time which has to be in the same format.
=CONCATENATE(HOUR(NOW()),":",MINUTE(NOW()),":",SECOND(NOW()))
You can put the whole lot in a single cell:
=RIGHT(LEFT('Bet Angel'!B1,SEARCH(":",'Bet Angel'!B1,1)+2),5)-CONCATENATE(HOUR(NOW()),":",MINUTE(NOW()),":",SECOND(NOW()))
You'll need to format the cell as time.
BA,
Thx, I copied and pasted the entire formula into a single cell and it works OK.
There is one small niggle, if the race is late off then the countdown timer becomes negative and it displays a series of hash symbols due to the negative time value.
Is anyone aware of any specific formatting code I can use to get the negative time to display with a minus sign and in red rather than the hashes ?
Rgds
Thx, I copied and pasted the entire formula into a single cell and it works OK.
There is one small niggle, if the race is late off then the countdown timer becomes negative and it displays a series of hash symbols due to the negative time value.
Is anyone aware of any specific formatting code I can use to get the negative time to display with a minus sign and in red rather than the hashes ?
Rgds
-
finnbarr99
- Posts: 35
- Joined: Sat Jun 27, 2009 1:17 pm
Youd need to make a custom format in excel.
As we are only dealing with hours/mins/secs, what about the 1904 date system?
http://j-walk.com/ss/excel/usertips/tip051.htm
http://j-walk.com/ss/excel/usertips/tip051.htm
The way I get around this is to just logic check the value. And if the time is less than zero just leave at zero. Then apply a conditional format to highlight the cell in red if it is less than 30 seconds for example.
=IF(Time<0,0,Time)
=IF(Time<0,0,Time)