• 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.

Schedule Difference

S

Svmaxcel

Member
We have to make schedule for our employee every week.
Our week ends on Saturday and Starts from Sunday.
They get 2 week off in a week.(not on fixed days)
We have several shift in a day, some starts in morning and some in afternoon/evening....
We change the schedule almost every week depending on call volumes.
So week off and shift gets changed for many employees.

I have attached a file, with an older schedule and this weeks schedule.
There are 2 sheets, Wk25 and Wk2 both have same formats.
For Wk25 sheet there are 2 extra column "Week off after " & "Shift conflict"
These 2 column will look for earlier week schedule and check for condition

Week off after column is to count the number of days an employee is getting a week off.
Example:
An employee had a week off on 14&15 June(wk24) and next week off is on 20&21 June(wk25), so employee is getting week off after 4 days.
So I need the count in this column.

Shift conflict occurs in case of Shift timing change of an employee.
Example
An employee is doing 18:00 - 03:00 on Saturday and if he gets a Shift timing of 08:00 - 17:00 on Sunday, he will not get proper rest.
As per our organization there should be a difference of at least 12 hours.
So in case I get "Yes" in shift conflict I would have to swap his shift with someone else.
 

Attachments

  • Book1.xlsx
    10.2 KB · Views: 5
Svmaxcel
Is there always five employees per week?
Are Emp Number and Manager seems to be fixed,
but Emp Name change weekly (not connected with Emp Number)?
Why You won't use one sheet for every week?
 
Employee count can range from 48 to 200, depending on volume.
The schedule will receive by WFM has the Shift time and week offs.
Emp ID.name/Manager are blank.
So we add the names to the schedule
EmpId/Name/Manager remains the same.
 
Is there always five employees per week?
Employees could changes every week.

Are Emp Number and Manager seems to be fixed,
Emp I'd of an emp always remains same, manager as well.

but Emp Name change weekly (not connected with Emp Number)?
My bad, I used Random formula to generate random Data, but emp I'd always remains the same.

Why You won't use one sheet for every week?
As you suggest

Note : in case we have low volumes, we schedule less employee and emp count might change.
Example : So in this week if there are 5 slots scheduled in 08:00 - 17:00, next week it might be possible that number of slots in thus shift will change.
Same goes with Week off.
Sometimes we do get request from employees for future weeks to change their week off and shift time, in this case sometimes we have to swap it with someone.
 
Svmaxcel
as written: You're continue using terms which You would open or don't use nor mix eg WFM.
Those won't help so far nothing!

> Here, a quick sample.
( Quick means not ready at all! )
 

Attachments

  • Book1.xlsx
    14.2 KB · Views: 9
Svmaxcel
eg: WFM, low volume, less employee and emp count .. same?, slots and
When gets the schedule by WFM that low volumes would effect that employees could request for future weeks?
 
Letme go step by step and give you an insight of our organization workings.

All numbers here are just examples.

Employees work on rotational shifts, I mean for 15 days they may have 06:00 - 15:00, but after 15 days we change it to 13:00 - 22:00 and so on, because others should also get a chance to work in morning shifts.
Every Emp has 2 week off in a week (not fixed)(day may change).
They work for 9 hours a day.

I work in a contact center (call center) and employees pick up incoming calls. We are working 24/7 to answer customers query

Every week, we get forecast of call numbers, like Day 1(Sunday) 100 calls, Day 2(Monday) 200 calls, etc...
WFM (Work Force Management) team views the call forecast for coming week and prepares and send us the schedule for coming week.

So lets say on 20th if forecast is high, they gives less Week offs, for maximum staffing, they also check call pattern for shift timings and finally sends the schedule to us.(scheduled part is completed done by them, its their lookout, we do not interfere in this)
The Emp Name/Manager are blank, In ID we have slot1, slot2, slot3...

Now in this i can only fill in the Emp details.(I'd,Name,Manager), we cannot alter any other field like Time and Week offs as its based on forecast.

We can give employees same week off/shift time as earlier week but there are 2 issues due to which we may change their shifts and Offs.

Issue 1 : Forecast
WK 24 had 5 morning shift, but WK 25 may have only 3 morning shift, so only 3 employees can work in morning shifts(in this case we will have to move rest 2 employees in other shifts)
Sometimes we do get 2 different shift timing for an employee, example Emp A has 06:00 - 1700 on Sunday/Monday, but may have 1300 - 2200 on rest of the days.

Issue 2 : sometimes employees ask for week off or shift time preference.
So if an employee is in evening shift may ask for morning shift or an employee having Thursday-Friday Week off may ask for Sunday-Monday.
In thus case we tell Emp we will try to accommodate their request, because it also depends on available slots.

I don't think there could be a way to automate the process of filling emp details.

So here I would just like you to know, if there shift timing is clashing and the number of days they would be working

I will be attaching a dummy data file, with real format.

In case of any doubt let me know

▬▬▬▬▬▬▬▬▬ Mod edit : duplicate post removed …
​
 
Svmaxcel
I got one answer ... thanks,
but others missed?
Why do You think that there cannot be a way to automate something?
... Your real format dummy data file ... seems ... still thinking?
You sent twice above reply ... or were there huge differences?
Without 'a quick sample' layout this would be challenge!
 
Svmaxcel
... no answers ...
That file ...
I would have many questions,
but if no answers then I won't add any.
Many things are still possible,
but if I have to dig out everything ... not good!
 
as mentioned earlier, i don't have too bother with forecast, as its taken care by other department, Also the numbers were just random..
I am concern about the scheduling file
 
vletm said:
Svmaxcel
as written: You're continue using terms which You would open or don't use nor mix eg WFM.
Those won't help so far nothing!

> Here, a quick sample.
( Quick means not ready at all! )
Click to expand...
i recovered a bit after the accident.
just checked the file, however did not understand logic

what is C5:I6 in Sheet1?
Also if i have 100+ employees, it would be difficult to search.

After making the roster, i have to submit the same to WFM team to upload the roster in our Tool, so they are not accepting this format.
they only need the format attached eaarlier.

Anyway, thanks for trying.
 
Svmaxcel
> Still no answers ...
> Forecast? ... You start to write it now ... why?
This sample let You mark those 'Issue 2-cases'.
> You gave those values, which WFM has done.
And after that something should do ... as my sample from You randoms #13.
If You always give everything as random ... it's challenge to do something real!
> C5:I6 like shift times, why need to write start & ending times to every cell?
> my sample (#13) has now places for 200 employees ...
What should search? Why something would be difficult?
> If they accept only random format then good luck
and You can continue to do those manually.
 
Svmaxcel
No,
because there have to be previous (or more weeks data to use) and
which order would those employees are...
Or what would You try to ask?
btw... You have shown two formats, which You wrote is impossible, only one is possible!
 
Svmaxcel said:
Uploaded
Click to expand...
Need this format.
Previous data would be in other sheet

Reason why I told that automation is not possible is because of below reasons.

Slots keeps changing as per shift time, so its erratic.
Week off are not same like earlier week.
Even employees sometimes ask for a preferred shift time or week off.


If you think automation is possible.
Can the slots be filled automatically based on previous week?

Slots means rows where we fill emp details.
 
Svmaxcel
Your: Can the slots be filled automatically based on previous week?
1) ... based ... do You mean 'copy&paste' ... hmm?
2) What about that WFM's information?
3) Questions needs answers.
4) If there are rules how to fill then many things are possible.
If no rules ... even then it would be possible.
But if You won't cannot tell those, then it would be challenge!
But, if it can do manually then the code would so same!
 
You must log in or register to reply here.
Back
Top