Link to home
Start Free TrialLog in
Avatar of Jon Crabtree
Jon Crabtree

asked on

Excel Work Rota - No Shift Patterns - Work Allocation Only

I have recently joined an office as the new manage. The office is a central point where all company data is returned for data quality checks and filing, the previous manager had been in role for many years and from what I have seen thus far, it seems that a lot of the processes are also many years old.

In the new role I have been looking over how processes are performed, one process that I am keen to cull or at least reduce in the extreme is the process of creating a weekly rota.
This currently takes around four hours for a member of staff to complete.

All staff are on flex-time so there is no issue with timing or shifts. People can start anytime between 0700 and finish at 2200, this is logged on a separate HR system and plays no part in the rota. There are around five tasks which require a varying amount of people each day. Recently there has been a massive uptake of new staff which means that only some of the staff are trained in all processes.

Process at present -

  • At present the staff member will check the HR system for annual/ flex leave, this data will then be transferred manually into a master excel spreadsheet, that has all names pre populated.
  • Tasks allocated to staff task by task, day by day.
  • Only certain staff can complete all tasks at present.
  • Training completed task by task, so flexibility to add staff tasks required.

Initially I thought that knocking up a spreadsheet to perform the above would take but an hour or so, unfortunately in several days I have not been able to work out any solution, even rough! My first thought was to use one tab to add all the staff members on, along with the tasks such as below.

Fig. 1

Fig1.xlsx

There is also a list of the ideal amount of people who should be performing each task, each day.

Fig. 2

Fig2.xlsx

The above numbers are rough at present, basically for the first three these are minimum numbers required during this training phase. The Audit is to be completed by any staff member who is not otherwise allocated a task. This ranges day to day dependent on leave.

As mentioned I had initially thought that with a couple of tabs setup that I could alter as staff numbers increase and they become further trained I would be able to work out the next part with ease. However, this has not proved the case and I am stumped! As yet I have not even been able to get any output let alone something that works. I had been hoping to push the schedule one down on a week by week basis which should make it as fair as possible whilst also reducing the time taken from several hours to minutes.

There are a lot of security concerns in my field and although I am able to run macros (due to other dependent programs), I am not able to run other programs or addons - I know there are other programs out there that could do this but, I unfortunately I am working with what I have got. There is also difficulty in sending items via email into the workplace. Removable media is not allowed.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jon Crabtree
Jon Crabtree

ASKER

As suggested by Gustav, I have put together a "Gig" which is enabling more work to be put into the project on a paid basis.