converting text in cell to a date time

H

Herman

I have a range say A4:A200 in cell A3 is the date and in the range there is a
bunch of cells that have a 3 up to 7 character text that equate to a time,
event, who and where code. I need something that will take the code and put
that code in a oneliner

ie code is 11P4 one liner should read today's date the 11 = 11am the
P =practice and the 4 is who and because there no character after the 4 the
location is known as Courtice with each peice of info in the one liner going
in it's own seperate cell

any help on this would be appreciated
 
R

Richard Buttrey

Can you give a bit more detail about the 3-7 character text items.

I'm assuming you can have something like 1P4 meaning 01:00 a.m., but
how do you get up to 7 characters?

Is the "P" code always a one character or more? and how many codes are
there in this position.

Similarly with the "4" code. how many people are there? up to 99 or
more?

Finally what is "Courtice". Is this always the same or is this to be
derived from a Lookup table based on the who?

Rgds


I have a range say A4:A200 in cell A3 is the date and in the range there is a
bunch of cells that have a 3 up to 7 character text that equate to a time,
event, who and where code. I need something that will take the code and put
that code in a oneliner

ie code is 11P4 one liner should read today's date the 11 = 11am the
P =practice and the 4 is who and because there no character after the 4 the
location is known as Courtice with each peice of info in the one liner going
in it's own seperate cell

any help on this would be appreciated
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
H

Herman

you are correct
some of the other codes are as follows

7AP4 = 7AM PRACTICE WITH 4
103H2 = 10:3OAM HOME GAME VS. 2
73AG3 = 7:30 AM GAME @ 3
73HN1N = 7:30PM HOME GAME VS N1 IN NEWCASTLE

The last character always refers to location and if no alpha character then
default is default location A there are 5 locations with only 4 being
identified as 5 = blank

time can be up to 4 characters with default always being a PM time except as
follows

7A = 7am, 73A = 7:30 am, 8A = 8am, 9A = 9am, 10 = 10am as no PM on Saturdays
or Sundays same applies to 11. 103 = 10:30 am, 11 = 11am, 113= 11:30 am, 12
= 12pm, 123 = 12:30pm, 1 = 1pm, 13 = 1:30pm and so on

the next character in the code of which there is only 3, refers to the event
P = practice H = Home game G = Away game

the next character(s) refer to the team which go anything from 1-22 as well
as N1, N2, N3, O1 and O2

hence you 7 charater code 103GN1O which equates to 10:30 away game vs.
Newcastle 1 in Orono and the date at the top of the column tells ya the
date.. hope this helps..
 
R

Richard Buttrey

Because of the varying length or the code this is an extremely
difficult one to slice into its correct constituent parts.
Particularly the last character of the code which appears from the
example you cite may be a place "Orino", or nothing, and in this case
the last character(s) represent the team numbers.

It seems to me that it would be far more practical to have a
consistent code, where the same characters mean the same sort of thing

The best I can come up with so far, is to use lookup tables. Hopefully
someone else could come up with a simpler suggestiom

One table called "Times" as follows


7A 7 AM
73A 7:30 AM
73 7:30 PM
8A 8 AM
83A 8:30 AM
9A 9 AM
93A 9:30 AM
10 10 AM
103 10:30 AM
11 11 AM
113 11:30 AM
12 12 PM
123 12:30 PM
1 1 PM
13 1:30 PM
2 2 PM
23 2:30 PM
3 3 PM
33 3:30 PM
4 4 PM
43 4:30 PM
5 5 PM
53 5:30 PM
6 6 PM
63 6:30 PM

And the other table called Game Type as follows

P Practice With
H Home Game vs.
G Away Game @

with the code in A1 put this in B1

=VLOOKUP(LEFT(A1,IF(ISERROR(SEARCH("P",A1)),0,SEARCH("P",A1))
+IF(ISERROR(SEARCH("H",A1)),0,SEARCH("H",A1))+
IF(ISERROR(SEARCH("G",A1)),0,SEARCH("G",A1))-1),times,2,FALSE)&"
"&VLOOKUP(MID(A1,IF(ISERROR(SEARCH("P",A1)),0,SEARCH("P",A1))
+IF(ISERROR(SEARCH("H",A1)),0,SEARCH("H",A1))
+IF(ISERROR(SEARCH("G",A1)),0,SEARCH("G",A1)),1),Game_Type,2,FALSE)

It returns the things below, but it does not yet have the last team
information because of the difficulties I noted above. It may give you
some ideas though since you understand the codes better than I do. You
may be able to slice off the appropriate last characters of the code,
and do another lookup in another table, and concatenate this last bot
onto the formula above.

Hope this helps in some way.

7 AM Practice With
10:30 AM Home Game vs.





you are correct
some of the other codes are as follows

7AP4 = 7AM PRACTICE WITH 4
103H2 = 10:3OAM HOME GAME VS. 2
73AG3 = 7:30 AM GAME @ 3
73HN1N = 7:30PM HOME GAME VS N1 IN NEWCASTLE

The last character always refers to location and if no alpha character then
default is default location A there are 5 locations with only 4 being
identified as 5 = blank

time can be up to 4 characters with default always being a PM time except as
follows

7A = 7am, 73A = 7:30 am, 8A = 8am, 9A = 9am, 10 = 10am as no PM on Saturdays
or Sundays same applies to 11. 103 = 10:30 am, 11 = 11am, 113= 11:30 am, 12
= 12pm, 123 = 12:30pm, 1 = 1pm, 13 = 1:30pm and so on

the next character in the code of which there is only 3, refers to the event
P = practice H = Home game G = Away game

the next character(s) refer to the team which go anything from 1-22 as well
as N1, N2, N3, O1 and O2

hence you 7 charater code 103GN1O which equates to 10:30 away game vs.
Newcastle 1 in Orono and the date at the top of the column tells ya the
date.. hope this helps..
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Top