Ken,

Wouldn't you do better to concentrate on forcing your users to enter times
in the correct format, rather than trying to process everything that they
might enter?

For example, couldn't you use dropdown lists within data validation that
only allowed in (say) column A a number between 00 and 23 (hours) and in
(say) column B only numbers between 0 and 59 (minutes). These columns could
thus contain your start times and you could use a similar concept in columns
C and D for end times.

Then, depending on your search time, you could to see if it was greater than
the start and end times for each employee.

I may be barking up the wrong tree here, but then again, it might help.

Regards

Pete


"Ken McLennan" wrote:

G'day there One & All once again,

I've got a small problem in what was supposed to be a quick
worksheet to manipulate a shift roster. I've managed to get most of my
ideas to operate in the real world and it was kind of coming together
until tonight.

My problem is that I'm dealing with about 50 different rosters
that are composed by 50 different people with no consistent format.
Hence I might find a 9am to 5pm shift entered as "9-5", "9a - 5p", "9A-
5P", or any other variant you may like to think of. There may also be
initials or symbols at beginning or end which signify various
reminders/notes/whatever. So there might well be something like "CT 9-
5", or "#9a- 5p!". The only consistency is the hyphen, which makes the
use of the Split function fairly simple. Many (read almost all) of the
entries are not validated and even the spaces may be omitted.

After splitting at the hyphen I've then tried stepping through the
resulting left & right hand strings, and removed all except the numeric
characters. Once again, that's fairly simple. Where my brain is starting
to hurt is in trying to determine the shift times in 24 hr format. What
I want to do is to nominate a time, say 14:30, and then step through all
the sheets and determine who is actually working at this time. That is,
if the nominated time is between the shift start & finish times then
list that entry.

The sheet entries are of this form:

Tuesday Wednesday -- any number of further columns.
01/01/05 02/01/05

6a-2p Trn 8a - 4p
8 -4 2p-10p !
2 - 10 2 - 10
!6p-2 Off

etc

Some rosters are 14 day, others are 28 although all will start on
the same day of the week.

I simply step through the sheets, and search for the nominated
date. I then work my way down the column until I reach the bottom row.
If the nominated time falls within the shift then I use that row to
extract the employees name, position and section from the first columns.

At least, that's the theory. I've come unstuck at how to parse the
strings to extract the times. It's not possible for me to enforce
uniformity on the manner of entry so I'm stuck with what are almost
random strings.

Google searches haven't produced the results I'd have liked. I've
found a squillion ways to extract time and or date in various formats,
but not from strings such as this!!

Can someone please offer a way me to extract what I need?
Conversion between data types isn't a problem. The issue is determining
the data to begin with. Oh, I can't install any dlls that will allow
regex searching either. Nor do I fully understand how to use such a
beast, but I saw it in the results from my searching.

Thanks for listening,
Ken McLennan
Qld Australia