• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Easier Data Validation help

T

teajuana

New Member
Hello, and thanks soo much for your time.


I would like to use Validation to ensure that I don't schedule an employee for a night shift and then the immediately succeeding or preceeding day shift and or visa versa. I am close to having what I want but some of the final details are giving me fits.


http://hotfile.com/dl/136753405/37faddb/2weekschedForum.xlsm.html


on this sample I think I have solved every possible error except it still allows me to schedule "John" for a Night shift immediately following a day shift on the same day. This is obviously not good for John:). Please take a look at my validations and tell me what I am missing.


Or, tell me an easier way to accomplish it. (you will see the validations are quite lengthy and hard to modify and or trouble shoot from within the validation setup).


Also, I am trying to figure out how to make the validations dependant on the name of the employee rather than have to make sure that the employee's day and night scedule is separated by a specific # of rows. I guess I am trying to make the validations relative to the name and not cell references.


thanks again
 
Hi ,


I tried going through your validation worksheet ; however , I would like to know what exactly you would like to do. As I understand it , you have employees , whose names are as follows :


Kathy ..... Russell ..... Lynne ..... Teresa

Lynn M ... Steve ....... Ron ........ Debera

Bill .......... John ........ Jennie ..... Robyn


Is there any reason why their names are listed like this ? Are some employees meant to work only with specific employees or can anyone work with anyone else ?


2. Against "John" , your worksheet has shifts such as 1 , 2 and "On call" ; what is this "On call" , and how is it to be treated as far as validation goes ?


3. You mention that all shifts are 12-hour shifts , but the validation formulae check for 1-6 ; what is this ?


If you can specify all the rules which you want to be implemented , we can arrive at the correct method of implementing them.


Narayan
 
That is a list of all employees in order of seniority. I eliminated all of them but John in order to simplify my question.


'ON Call' indicates a day which the driver is supposed to be available for call in just in case we have extra work or someone calls off sick. The driver should not be eligible to be "on Call" for a shift immediately predeeding or succeeding a scheduled work shift.


Nights = shift that begins in the PM

Days = shift that begins in the AM


All shift are 12 hours unless otherwise noted, 1-6 in the "Days" schedule means the employee would work from 1am to 6am likewise 10-4 in the Nights schedule would indicate 10pm to 4am as seen in the following link. These are what works for my current needs, it would be great if the formulae or validation would allow for other partial shifts i.e. 8-6 or other as business needs change.


http://hotfile.com/dl/136813238/6101b3a/2weeksched2.xlsx.html


I would like to use Validation (or some other means) to ensure that I don't schedule an employee for a night shift and then the immediately succeeding or preceeding (or in fewer than 8 hours) day shift and or visa versa. Not sure how to specify exacly what I would like but I will try.


Driver who works 1, 2, 3, 4, 5 etc, etc, oclock 12 hour shift cannot be scheduled or be on call for the immediately preceeding or succeeding shift, must have atleast 8 hours off before and after the shift.


Driver who is scheduled off is eligible to be "on call" or have a scheduled shift (i.e. 1, 2, 3, 10-4, 1-6 etc. immediately preceeding or succeeding the "Off" shift.


If driver "Off" on Days then can be scheduled 1, 2, 3...... or OnCall on Nights, if this is the case the driver cannot be scheduled for the following Day shift.


If driver "off" on Nights then can be scheduled 1, 2, 3...... or OnCall on Days, if this is the case the driver cannot be scheduled for the following Night shift.


Thanks once again Narayan.
 
Hi ,


I am still trying to come to grips with this problem.


Can we make a matrix of possibilities ? Correct me if I am wrong , but you will start scheduling a driver from day 1 of your schedule ; on the first day , any time is possible , but thereafter , every day’s schedule will be validated against the previous day’s schedule. Is this correct ?


Going by the different schedules that are already present in your worksheet , the following are all possible :


ON CALL , OFF , DAY , NITE : out of these DAY and NITE can be filled in through formulae , but ON CALL and OFF will have to be manually entered.


1-6 , 10-6 , 10-4 , 2 , 3 , 4 , 5 : these will all be manually entered.


ON CALL AFTER 6 : to be manually entered.


Assuming that these 10 possibilities are the only ones possible , can we have a matrix , with these 10 possibilities in 10 rows , and the same 10 possibilities in 10 columns across. Within the matrix , you fill in a 1 where the column entry is possible after a row entry on the previous day , or a 0 where the column entry is not possible.


Let us consider the following example :


If driver "Off" on Days then can be scheduled 1, 2, 3...... or OnCall on Nights, if this is the case the driver cannot be scheduled for the following Day shift.


Suppose now the entry is OFF on day 1 ; thereafter , on the next day or night , I am able to see only a 2 , 3 , 4 or ON CALL ; why are there no 1 , 5 , 1-6 , 10-4 , 10-6 on the next day after an OFF day ?


Is an OFF day to come after every 4 days of shift duty ?


Do any of the drivers have their fixed OFF days or any kind of preference for Day shifts or Night shifts ? Some names are present only in the Days schedule , while others are present only in the Nights schedule.


Sorry if I am asking too many questions !


Narayan
 
Questions are not a problem. I just hope I can answer them clearly. It's 10:30 here, time to go home. I'll work on answers tomorrow.


As always Thanks!
 
Can we make a matrix of possibilities ? Correct me if I am wrong , but you will start scheduling a driver from day 1 of your schedule ; on the first day , any time is possible , but thereafter , every day’s schedule will be validated against the previous day’s schedule. Is this correct ?


-------Not sure I understand what a matrix is, but I believe the answer would be YES----


Going by the different schedules that are already present in your worksheet , the following are all possible :


ON CALL , OFF , DAY , NITE : out of these DAY and NITE can be filled in through formulae , but ON CALL and OFF will have to be manually entered.


1-6 , 10-6 , 10-4 , 2 , 3 , 4 , 5 : these will all be manually entered.


---------Yes, but if possible I don’t want to be locked into just these options.-----------


ON CALL AFTER 6 : to be manually entered.


---------Yes------------


Assuming that these 10 possibilities are the only ones possible , can we have a matrix , with these 10 possibilities in 10 rows , and the same 10 possibilities in 10 columns across. Within the matrix , you fill in a 1 where the column entry is possible after a row entry on the previous day , or a 0 where the column entry is not possible.


Let us consider the following example :


If driver "Off" on Days then can be scheduled 1, 2, 3...... or OnCall on Nights, if this is the case the driver cannot be scheduled for the following Day shift.


Suppose now the entry is OFF on day 1 ; thereafter , on the next day or night , I am able to see only a 2 , 3 , 4 or ON CALL ; why are there no 1 , 5 , 1-6 , 10-4 , 10-6 on the next day after an OFF day ?


-------Just happens that on day 1 we need only 3 day shifts; 2 oclock, 3 oclock, and 4 oclock A.M. to the same in the P.M. as these are 12 hours shifts. Likewise on night shift 2,3,4 oclock P.M. to the same in the following A.M. again 12 hour shift. Currently Thursdays require 1 extra part time shift (10-4) on nights, Friday and Saturday require 1 extra 12 hour shift (5pm – 5am) and one part time shift 10pm – 6am. Saturday and Sunday mornings from 1am – 6am.--------------


Is an OFF day to come after every 4 days of shift duty ?


--------Not necessarily, we have recently implemented a 4on 3off 5on 2off schedule for the primary full time drivers, whose names appear only once, either Days or Nights. The other shifts are part time and rotating shift positions, which is why some names appear in both the Days schedule and the Nights schedule.----------


Do any of the drivers have their fixed OFF days or any kind of preference for Day shifts or Night shifts ? Some names are present only in the Days schedule , while others are present only in the Nights schedule.


--------------------Basically, at this time the work days and times and the days off will follow the same pattern repeating every 2 weeks, but is occasionally changed due to holidays, community events and employee vacations where one or more drivers might be have a scheduled work time where normally they would be OFF or OnCall.-------------


I hope I have answered your questions well enough for yo understand. Thanks again.
 
Hi ,


Can you check out the worksheet at the following link :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21120


It is a blank worksheet , with some labels in column A , and the same labels in row 2 , from cell B2 onwards.


If we can fill in the cells A3 onwards with either a 1 signifying TRUE or VALID , or with a 0 signifying FALSE or INVALID , then this matrix can be used to validate the data entries in your schedules.


Of course , the interconnection between the Day shifts and Night shifts will have to be done separately , but we can come to that later.


Is it possible you can fill in this worksheet ?


Narayan


P.S. Do not click on the hyperlink ; that is only a part of the address ; instead , copy the complete address , and paste it in your browser.
 
Ok, I completed it. Not sure I am wrapping my simple mind around this matrix thing though(sorry:( ).


http://hotfile.com/dl/136961760/c142711/MATRIX_OF_VALID_SCHEDULING.xlsx.html
 
You must log in or register to reply here.
Back
Top