Extracting Time Format query

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

Extracting Time Format query

Postby Tony Grant » Mon Apr 15, 2013 2:11 pm

Hi, I am using this excel formula to extract the event time from cell A! on the market tab:

=MID(A1,FIND("-",A1)+2,5)

which basically opens up the string, goes along to the - sign, moves another 2 chars along then pulls the next 5 chars which is the time (i.e. 14:30 )

the trouble I am having is this is not in a time format and I cannot seem to convert it into this with any formatting command. I need it to be as a time so that I can perform lookups against my selection sheets or such like.

Does anyone know how to do this please?

Alternatively, if there was another reliable place where the event time is set alone then maybe this would be better but I haven't noticed anywhere.

thanks for any help.

Tony
Tony Grant
 
Posts: 6
Joined: Mon Jan 14, 2013 4:43 am
Location: Lincoln, UK

Postby osknows » Mon Apr 15, 2013 2:40 pm

It could be that the extracted time from A1 is a string but the data in columns is date format. So you may need to convert either the match value or array to the same types before performing the lookup

Eg take the time 14:15

DateType: 0.59375 (but formatted as hh:mm would show 14:15)
StringType: 14:15

convert string to time =VALUE("14:15")
convert date to string =TEXT(0.59375,"hh:mm")
User avatar
osknows
 
Posts: 946
Joined: Wed Jul 29, 2009 12:01 am

Time String

Postby Tony Grant » Tue Apr 16, 2013 7:41 am

Hi,

Yeah I have tried converting the formats in every way I can think of but it still doesn't validate in a vlookup.

For whatever reason, the extracted text just seems to refuse to be changed.

I have heard that the latest versions of Excel have a TIMEVALUE() function so I might try that next.

It is a total mystery why this is happening really lol.
Tony Grant
 
Posts: 6
Joined: Mon Jan 14, 2013 4:43 am
Location: Lincoln, UK

Time String

Postby Tony Grant » Tue Apr 16, 2013 7:51 am

Ok, got it sorted thanks.

For anyone wanting to do the same, this is the way:
First extract the time using:
=MID(A1,FIND("-",A1)+2,5)
then in another cell, convert that extracted text string to a usable time format as follows:
=TIMEVALUE(B1) (where B! is the location of the extracted time string)
This returns a decmal value for the time and can be used in a vlookup formula or changed to whatever time format you now require such as H:MM etc
The TIMEVALUE() formula seems to be the key here as simply formatting the extracted time string as 'TIME' does not appear to work.

Thanks for helping out on this one :D

I will now move forward to my next hurdle in about TIMEVALUE=5 minutes

Tony Grant
Tony Grant
 
Posts: 6
Joined: Mon Jan 14, 2013 4:43 am
Location: Lincoln, UK


Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 6 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

cron