S
Steve
I currently using an old DOS program that creates day off schedules for
employees.
I need to create the same thing in Excel.
Working with a 7 day workweek, and numerous scenarios, such as:
Sat Sun Mon Tue Wed Thu Fri
Req 17 0 26 29 26 26 26
By entering the above data, the results are as follows:
Sat/Sun 13
Sun/Mon 4
Mon/Tue 0
Tue/Wed 0
Wed/Thu 0
Thu/Fri 0
Fri/Sat 0
Sun/Tue 1
Sun/Wed 4
Sun/Thu 4
Sun/Fri 4
Total 30
Indicating that I need 13 employees with Sat/Sun off, 4 with Sun/Mon off, etc.
If the preliminary result is not nicely divided by 5 ( 5 workdays per week),
the program will request additional input, such as: you must understaff by 2
employees, or overstaff by 3, or 4 and 1, or 3 and 2, etc. ( all over/under
=ing 5)
Such as:
Sat Sun Mon Tue Wed Thu Fri
Req 4 4 4 4 4 4 4
And indicating that I had to overstaff by 2, or understaff by 3. I chose
overstaffing by 2 (thru & fri)
resulting in:
Sched 4 4 4 4 4 5 5
Which produces:
Sat/Sun 1
Sun/Mon 1
Mon/Tue 1
Tue/Wed 1
Wed/Thu 1
Thu/Fri 0
Fri/Sat 1
Sun/Tue 0
Sun/Wed 0
Sun/Thu 0
Sun/Fri 0
Total 6
The other thing I need is to have another option as follows: Consecutive
days off ( Sat/Sun, Sun/Mon, etc) are the priority, however, if the program
cannot produce all schedules with consecutive days off, it prompts for "
Cannot make consecutive days off, do you want some split days off". I need to
be able to answer Yes or No ( sometimes split days off are ok, sometimes they
are not).
E.g., the first scenario, when answered that I do NOT want split days off,
produced this:
Sat Sun Mon Tue Wed Thu Fri
Req 17 0 26 29 26 26 26
Sched 17 11 26 30 28 26 27
Sat/Sun 15
Sun/Mon 7
Mon/Tue 0
Tue/Wed 3
Wed/Thu 2
Thu/Fri 5
Fri/Sat 1
Sun/Tue 0
Sun/Wed 0
Sun/Thu 0
Sun/Fri 0
Total 33
I surely hope that I explained this properly ( I realize it may be a bit
confusing),and I'm guessing that it's probably a lot easier than what it
seems, but I don't have a clue how or where to even begin. Any help in
creating this tool would be greatly appreciated.
Thanks,
Steve
employees.
I need to create the same thing in Excel.
Working with a 7 day workweek, and numerous scenarios, such as:
Sat Sun Mon Tue Wed Thu Fri
Req 17 0 26 29 26 26 26
By entering the above data, the results are as follows:
Sat/Sun 13
Sun/Mon 4
Mon/Tue 0
Tue/Wed 0
Wed/Thu 0
Thu/Fri 0
Fri/Sat 0
Sun/Tue 1
Sun/Wed 4
Sun/Thu 4
Sun/Fri 4
Total 30
Indicating that I need 13 employees with Sat/Sun off, 4 with Sun/Mon off, etc.
If the preliminary result is not nicely divided by 5 ( 5 workdays per week),
the program will request additional input, such as: you must understaff by 2
employees, or overstaff by 3, or 4 and 1, or 3 and 2, etc. ( all over/under
=ing 5)
Such as:
Sat Sun Mon Tue Wed Thu Fri
Req 4 4 4 4 4 4 4
And indicating that I had to overstaff by 2, or understaff by 3. I chose
overstaffing by 2 (thru & fri)
resulting in:
Sched 4 4 4 4 4 5 5
Which produces:
Sat/Sun 1
Sun/Mon 1
Mon/Tue 1
Tue/Wed 1
Wed/Thu 1
Thu/Fri 0
Fri/Sat 1
Sun/Tue 0
Sun/Wed 0
Sun/Thu 0
Sun/Fri 0
Total 6
The other thing I need is to have another option as follows: Consecutive
days off ( Sat/Sun, Sun/Mon, etc) are the priority, however, if the program
cannot produce all schedules with consecutive days off, it prompts for "
Cannot make consecutive days off, do you want some split days off". I need to
be able to answer Yes or No ( sometimes split days off are ok, sometimes they
are not).
E.g., the first scenario, when answered that I do NOT want split days off,
produced this:
Sat Sun Mon Tue Wed Thu Fri
Req 17 0 26 29 26 26 26
Sched 17 11 26 30 28 26 27
Sat/Sun 15
Sun/Mon 7
Mon/Tue 0
Tue/Wed 3
Wed/Thu 2
Thu/Fri 5
Fri/Sat 1
Sun/Tue 0
Sun/Wed 0
Sun/Thu 0
Sun/Fri 0
Total 33
I surely hope that I explained this properly ( I realize it may be a bit
confusing),and I'm guessing that it's probably a lot easier than what it
seems, but I don't have a clue how or where to even begin. Any help in
creating this tool would be greatly appreciated.
Thanks,
Steve