Discern a value from Two Columns in VBA

I'm sure this has been asked before, and I have searched every avenue high and low before asking.
I have two columns that contain text number representing times, for my purpose I don't really need to change them to actual times, column A contains a start time and column B and end time, i am trying (and failing in VBA) to discern what would be an Early, Late or Night shift. Early commencing 600 (06:00) to 1059 (10:59), Night finishing after 300 (03:00) to 0800 (08:00). Shifts are no more than 12 actual hours in length and will result in either and E, L or N in another column as a result (no formula). Late runs between 1100 to 259, and not all shifts are full lengths (my problem).
what I need is VBA that will do the comparison between two columns in a single pass (I seem to currently be running four loops in my tests, which provide a result, but are quite variable.

Can someone please prod me in the right direction

attach your sample workbook with your required output and I'll take a look.

Originally Posted by MatrixMan

attach your sample workbook with your required output and I'll take a look.

Attached now, struggled to get this really small, until i copied and pasted cells and VBA in the book, happy to discuss on email rather than tying up lists time. Thanks for looking

Originally Posted by mhuston

I tried to enter this in a spreadsheet and got confused with "commencing", "finishing" and "runs from"; what time does each shift start and end?

Shifts all vary in length and staff might work proportions of shifts dependant on leave, typical shifts are 7 to 3, 8 to 4, 7 to 7. A number run all night. When we report the shifts, the standard convention is to look at early, late and night, a snap shot of shifts looks like this
0000-0200
0100-0200
0100-0500
0300-0700
0330-0630
0600-1400
0600-1500
0600-1600
0600-1800
0630-1430
0630-1600
0630-1630
0630-1830
0645-1845
0700-0900
0700-1000
0700-1200
0700-1400
0700-1500
0700-1600
0700-1630
0700-1700
0700-1800
0700-1900
0730-1530
0730-1630
0730-1930
0800-0900
0800-1500
0800-1600
0800-1700
0800-1800
0800-1900
0800-2000
0830-1830
0900-1500
0900-1600
0900-1700
0900-1800
0900-1900
1000-1900
1000-2200
1100-2200
1100-2300
1200-0000
1200-1430
1800-0000
1200-1600
1300-0100
1400-0000
1400-0100
1400-0200
1400-1500
1400-1600
1400-2300
1430-2230
1430-2330
1500-0000
1500-0100
1500-0200
1500-0300
1500-1600
1500-1900
1500-2200
1500-2300
1530-0100
1530-0330
1600-0000
1600-0100
1600-0200
1600-0400
1600-1900
1600-2300
1700-0000
1700-0100
1700-0200
1700-0500
1700-1800
1700-1830
1800-0200
1800-0300
1800-0600
1830-0200
1830-0630
1845-0645
1900-0300
1900-0600
1900-0700
1900-2000
1900-2300
1930-0730
2000-0700
2000-0800
2100-0700
2230-0630
2300-0200
2300-0300
2300-0700

OK - so I think I understand what you're after ... the problem is that you've not uniquely defined Early, Late & Night such that they are mutually exclusive and inclusive of the full possible range. If you can do that, then I think it's pretty straight-forward and we can just use the column you've labelled VARIATION which has the start-end times to apply the ruleset.

You've said:
Early = commencing between 0600-1059
Late = running between 1100-2359
Night = finishing between 0300-0800

You see the problem? The definitions aren't exhaustive or exclusive.

Early & Late shifts are mutually exclusive only if you allow that both are defined by Early starting between 0600-1059 and it doesn't matter when they finish; Late starting after 1100 and ending before 2359.

That is:
Early starts between 0600-1059 and end time is irrelevent;
Late starts and finishes between 1100-2359.
So a shift from 1030-1200 is an Early? If so then the start time is the key and the arrangement between these two is fine.

Then there's Nights and here's where the real problems begin: You say these finish between 0300-0800 (and I infer from the description that start time is irrelevent). This does not provide coverage for the full 24hrs.

What about these shifts:
1030-0330 (possible unless shifts are never > 12hrs).
0100-0200 (outside any start-end time def'n)
2300-0200 (finishes too late for a Late; finishes too soon for a Night)
2330-0830 (finishes too late for either a Late or a Night)

I'm sure there are others, but you get the idea ... can you shed some light? Before we go coding a ruleset, it would be best to specify the logic in quasi-code; if one ruleset has priority over another, then this would be helpful too. Hope that helps, but maybe it doesn't. MM.

Originally Posted by MatrixMan

OK - so I think I understand what you're after ... the problem is that you've not uniquely defined Early, Late & Night such that they are mutually exclusive and inclusive of the full possible range. If you can do that, then I think it's pretty straight-forward and we can just use the column you've labelled VARIATION which has the start-end times to apply the ruleset.

You've said:
Early = commencing between 0600-1059
Late = running between 1100-2359
Night = finishing between 0300-0800

You see the problem? The definitions aren't exhaustive or exclusive.

Early & Late shifts are mutually exclusive only if you allow that both are defined by Early starting between 0600-1059 and it doesn't matter when they finish; Late starting after 1100 and ending before 2359.

That is:
Early starts between 0600-1059 and end time is irrelevent;
Late starts and finishes between 1100-2359.
So a shift from 1030-1200 is an Early? If so then the start time is the key and the arrangement between these two is fine.

Then there's Nights and here's where the real problems begin: You say these finish between 0300-0800 (and I infer from the description that start time is irrelevent). This does not provide coverage for the full 24hrs.

What about these shifts:
1030-0330 (possible unless shifts are never > 12hrs).
0100-0200 (outside any start-end time def'n)
2300-0200 (finishes too late for a Late; finishes too soon for a Night)
2330-0830 (finishes too late for either a Late or a Night)

I'm sure there are others, but you get the idea ... can you shed some light? Before we go coding a ruleset, it would be best to specify the logic in quasi-code; if one ruleset has priority over another, then this would be helpful too. Hope that helps, but maybe it doesn't. MM.

Nice concise summary. OK, would have to go for best fit, which is why i want to look at column one then column two and put a predefined result in column 3 (Do While Else or something like that, I can't achieve it on the form itself in standard formula because i was trying AND). Happy to add broad variations as they occur and if i set, isnotnull in the final cell, when the best option has occurred, that also is a way to go.

Standard shifts are between 6 and 12 hours, but on say an 8 hour shift you can take 4 hours leave.

As you stated E, L and N is pretty clear, and that would be optimum. No regular shift starts beyond midnight until 0600.

I didnt think it important, but this is to do with, weather one or two people are on a vehicle, so UM is none and SM means ideally we need to find a partner for that person.

There is a fourth variable, that does crop up, this sheet is developed in the morning between 7 and 8. Where a proportion on the shift occurs after midnight, the data is all from the same day and therefore history rather than future therefore 300 to 600 would be represented by another symbol (-N-) or similar.

the code dosen't have to look pretty, just read two columns, and depending on the logic and symbol assigned past that value in say an empty cell.

you will already have noted my coding is messy, it does the job it needs to and no more

Thank You

Originally Posted by Maurice

Nice concise summary. OK, would have to go for best fit, which is why i want to look at column one then column two and put a predefined result in column 3 (Do While Else or something like that, I can't SNIP

1030-0330 (possible unless shifts are never > 12hrs). Abnormal
0100-0200 (outside any start-end time def'n) Night
2300-0200 (finishes too late for a Late; finishes too soon for a Night) Would fit in Late
2330-0830 (finishes too late for either a Late or a Night) Night

just so you are clear

OK - so now I think we're getting somewhere

From:
Early = commencing between 0600-1059
Late = running between 1100-2359
Night = finishing between 0300-0800

And applying the logic of the exceptions:
1030-0330 Abnormal
0100-0200 (outside any start-end time def'n) Night
2300-0200 (finishes too late for a Late; finishes too soon for a Night) Would fit in Late
2330-0830 (finishes too late for either a Late or a Night) Night

Then can I apply logic in the following order?
Early = starting between 0600-1059
Late = start-finish from 1100-2359
Night = finishing between 0300-0800
Late = start-finish from 1100-0300
Night = finishing between 0000-0300
And anything not in this ruleset = Abnormal?

This is easily accommodated as a sequential ruleset via a Case statement; let me know if the above is correct and I'll code it up.

Originally Posted by MatrixMan

OK - so now I think we're getting somewhere

From:
Early = commencing between 0600-1059
Late = running between 1100-2359
Night = finishing between 0300-0800

And applying the logic of the exceptions:
1030-0330 Abnormal
0100-0200 (outside any start-end time def'n) Night
2300-0200 (finishes too late for a Late; finishes too soon for a Night) Would fit in Late
2330-0830 (finishes too late for either a Late or a Night) Night

Then can I apply logic in the following order?
Early = starting between 0600-1059
Late = start-finish from 1100-2359
Night = finishing between 0300-0800
Late = start-finish from 1100-0300
Night = finishing between 0000-0300
And anything not in this ruleset = Abnormal?

This is easily accommodated as a sequential ruleset via a Case statement; let me know if the above is correct and I'll code it up.

MatrixMan, What you propose sounds like it will fit 99% of the time and I am sure i could understand and extend with practice should abnormal occur too often. Thanks for getting involved

OK - try this out ... I think it's right and at least it gets the correct shifts per your example book. You can probably work out any tweaks based on the fuller dataset, but if you need a hand, just let me know.

Please Login or Register  to view this content.

Hope that helps. MM.

Originally Posted by MatrixMan

OK - try this out ... I think it's right and at least it gets the correct shifts per your example book. You can probably work out any tweaks based on the fuller dataset, but if you need a hand, just let me know.

Please Login or Register  to view this content.

Hope that helps. MM.

Perfect at 99.999999
I ran it against 118 variations a s shown above and it missed against 4 in total, all with a 300 finish

I added "=" sign to Case Is < 300 line 4 below for 100%

Select Case intEnd
Case 300 To 800
strShiftType = "NIGHT"
Case Is <= 300
If intStart >= 1100 Then
strShiftType = "LATE"
ElseIf intEnd >= 0 And intEnd < 300 Then
strShiftType = "NIGHT"

Thank you so much for thinking about this, i would have NEVER got there. http://www.excelforum.com/images/smilies/eek.gif

Maurice,

Please read forum rules below and then go back and edit you're post to include code tags

VBA Noob