LenaWood
asked on
Dupont Work Schedule
This is a "Just For Fun" type of a database. If it works, then I am sure it will be something that is very useful to my fellow co-workers.
At the Chemical Depot we work the Dupont Shift Work schedule with 4 crews (A, B, C, D)
T W T F S S M
Week 1 - N N N N O O O
Week 2 - D D D O N N N
Week 3 - O O O D D D D
Week 4 - O O O O O O O
D = Days; N = Nights; O = Off
About 51% of the people that work this schedule enjoy it (especially the 7 Days off on week 4)
Tuesdays are the start of our weeks as listed above (I am not sure who has the calendar where the week starts on a Tuesday...but OK.
June 1, 2004 the weeks were being started as follows:
A Crew - Week 3
B Crew - Week 2
C Crew - Week 4
D Crew - Week 1
OK....so the same person with the mixed up calendar picked the order that they crews would do this - it would have been too logical for Crew A to be week 1, Crew B be week 2 and etc.
I would like to be able to find out who is working any day of any year. Right now it is calculated out on an Excel Spreadsheet and usually only for the year you are on (So we can only see 2004 right now) and some time before the end of the year...someone sits down at Excel and does the next year.
Can this be automated so that a person could enter a date and have it tell you what crews will be working?
Again...this is a "hmmmm wonder if it can be done" type of thing.
Lena
At the Chemical Depot we work the Dupont Shift Work schedule with 4 crews (A, B, C, D)
T W T F S S M
Week 1 - N N N N O O O
Week 2 - D D D O N N N
Week 3 - O O O D D D D
Week 4 - O O O O O O O
D = Days; N = Nights; O = Off
About 51% of the people that work this schedule enjoy it (especially the 7 Days off on week 4)
Tuesdays are the start of our weeks as listed above (I am not sure who has the calendar where the week starts on a Tuesday...but OK.
June 1, 2004 the weeks were being started as follows:
A Crew - Week 3
B Crew - Week 2
C Crew - Week 4
D Crew - Week 1
OK....so the same person with the mixed up calendar picked the order that they crews would do this - it would have been too logical for Crew A to be week 1, Crew B be week 2 and etc.
I would like to be able to find out who is working any day of any year. Right now it is calculated out on an Excel Spreadsheet and usually only for the year you are on (So we can only see 2004 right now) and some time before the end of the year...someone sits down at Excel and does the next year.
Can this be automated so that a person could enter a date and have it tell you what crews will be working?
Again...this is a "hmmmm wonder if it can be done" type of thing.
Lena
ASKER
Maybe I am not reading this correctly...but will this allow me to have a Form with an unbound textbox. The user would enter a date...say 12/25/05 and know what shift is working days and what shift is working nights?
In the end I can picture using this to create a schedule for any time frame the user may want to enter. For example a calendar type of report that would have Days - Shift, Nights - Shift for every day in between two dates.
This would be useful for those wanting to schedule days off...or managers to be able to see what shift is working when some day in the future.
Lena
In the end I can picture using this to create a schedule for any time frame the user may want to enter. For example a calendar type of report that would have Days - Shift, Nights - Shift for every day in between two dates.
This would be useful for those wanting to schedule days off...or managers to be able to see what shift is working when some day in the future.
Lena
Yup,
The form would need to have one unbound field (a date entry field or date picker), a submit button, and four output fields (one for each team).
The control source for the output fields would be
=Dupont(DateEntryField, "A")
=Dupont(DateEntryField, "B")
=Dupont(DateEntryField, "C")
=Dupont(DateEntryField, "D")
and the submit button would need an OnClick event that simply refreshes the form (thus updating the output fields). The sample function would need to be able to handle NULL date values (which is why UserDate and Team parameters are specified as a Variant).
The sample function also needs to be modified to handle malformed date values. The OnClick event should look for return values from the function that indicate this and pop up a message box.
You could also modify the function so that the Team is passed in as a number instead of text, thus simplifying the code.
The form would need to have one unbound field (a date entry field or date picker), a submit button, and four output fields (one for each team).
The control source for the output fields would be
=Dupont(DateEntryField, "A")
=Dupont(DateEntryField, "B")
=Dupont(DateEntryField, "C")
=Dupont(DateEntryField, "D")
and the submit button would need an OnClick event that simply refreshes the form (thus updating the output fields). The sample function would need to be able to handle NULL date values (which is why UserDate and Team parameters are specified as a Variant).
The sample function also needs to be modified to handle malformed date values. The OnClick event should look for return values from the function that indicate this and pop up a message box.
You could also modify the function so that the Team is passed in as a number instead of text, thus simplifying the code.
ASKER
So I am only going to be able to look up one day at a time?
Lena
Lena
Lena,
the number of days that can be looked up at a time depends entirely on how you imlpement the function. The function returns one shift result for a given date AND crew. To get another return you need to change the date or the crew. Keep doing this until you've satisfied your requirements. Implementing it is up to you.
the number of days that can be looked up at a time depends entirely on how you imlpement the function. The function returns one shift result for a given date AND crew. To get another return you need to change the date or the crew. Keep doing this until you've satisfied your requirements. Implementing it is up to you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for updating the function. I will take a look at this at home as I am leaving work to go pick up my daughter.
I will let you know how things work out.
Lena
I will let you know how things work out.
Lena
ASKER
I believe that my question has been answered....Yes...it can be done. I will play with this a bit more and see what I can figure out. I am sure I will have more questions but will open a new question when the time comes to ask.
Rockmuncher - you are terrific! Thanks :-)
Rockmuncher - you are terrific! Thanks :-)
Aww Gawrsh (blushing). Thanks Lena!
Sometimes "Just For Fun" questions can be just that.
Good luck with it
Sometimes "Just For Fun" questions can be just that.
Good luck with it
Public Function Dupont(UserDate As Variant, Team As Variant)
Const Shifts = "NNNNOOODDDONNNOOODDDDOOOO
Const SourceDate = #6/1/2004#
Dim CrewOffset As Long
Dim OffsetDays As Long
' Crew offsets in days. Crew A= Offset(1)
Dim Offset(10) As Long
Offset(1) = 14
Offset(2) = 7
Offset(3) = 21
Offset(4) = 0
' add error handling here to ensure that UserDate and Team contain valid values
CrewOffset = Asc(UCase(Team)) - 64
OffsetDays = (SourceDate - UserDate)
If UserDate < SourceDate Then OffsetDays = 28 - OffsetDays
OffsetDays = OffsetDays Mod 28 + Offset(CrewOffset) + 1
Dupont = Mid(Shifts, OffsetDays, 1)
End Function
Call with something like Dupont(#1-Jun-2004#,"b").