On Tue, 14 Feb 2006 23:02:05 +1000, 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




Extracting the numbers is simple using regular expressions.

Your strings appear to be consistent in that there are only two sets of numbers
-- one representing a start time and one representing an end time.

The problem you will run into, given what you have posted so far, is
determining AM vs PM.

You have one entry '10-2'.

Since your goal is to enter a time and see who is working at that time, how do
you know if this represents 10AM-2PM vs 10PM-2AM ?

A similar argument could be made with other, inconsistent entries.

In any event, to extract the numbers simply, download and install Longre's free
morefunc.xll add-in from: http://xcell05.free.fr/


First Time: =REGEX.MID(A3,"\d+")
Second Time: =REGEX.MID(A3,"\d+",2)

or the same with the letter suffix (if present):

First: =REGEX.MID(A3,"\d+[APap]?")
Second: =REGEX.MID($A3,"\d+[APap]?",2)

The above expressions seem to handle the variations you posted. If you have
other variations, post them and we can handle them.

These formulas will also work in VBA:

For example, given your data:

================================
Option Explicit
Sub ExtractTimes()
Dim T(7) As String
Dim i As Long
Dim s1 As String, s2 As String

T(0) = " 6a-2p"
T(1) = " Trn 8a - 4p"
T(2) = "8 -4"
T(3) = "2p-10p !"
T(4) = "2 - 10"
T(5) = "2 - 10"
T(6) = "!6p-2"
T(7) = "Off"

For i = 0 To UBound(T)
s1 = Run([regex.mid], T(i), "\d+[APap]?")
s2 = Run([regex.mid], T(i), "\d+[APap]?", 2)

Debug.Print s1 & "....." & s2
Next i
=========================

6a.....2p
8a.....4p
8.....4
2p.....10p
2.....10
2.....10
6p.....2
......
--------------------------


You still have the problem of resolving the inherent ambiguity in some of the
entries.
--ron