Creating a list schedule from a grid table

[Oliver_watkins]

Hi! I am a manager in a department and have inherited a scheduling system I am trying to streamline/ automate. Can anyone help please?

I have two schedules I need to produce for the same 20+ employees every two weeks.

The first is a “table” like schedule. This is posted in the staff room for staff to see what their shifts are (“original schedule” tab in the attached file.) It has employees down the side and date along the top.

The second is a “List” type schedule (“Employee Schedule report” tab) which I need to produce to upload to our payroll system so that it can be checked against the clocking in/ out device so salaries can be calculated. Each line is one day for one member of staff.

The “Original Schedule” is the first one created and amended by the supervisor when staff change their days off, go sick etc. There is some conditional formatting to make it easier to refer to. The normal file is a table for the whole year. I have copied just one two week pay period for the example.

The “Employee Schedule report” needs to be sent to the payroll department at the beginning and end of every pay period (with any changes from the beginning.) I would like to be able to create this automatically using the “Original schedule” as the source data. I have applied some conditional formatting to this too just to make it easier to error check when inputting.

I have tried using a pivot table to create the “Employee Scheduling Report” automatically but then get stuck updating it and automatically splitting columns and inputting the other bits in different columns.

I feel like I can do most of the formulas/ procedures separately, but when I try and add it all together in one and make it happen automatically, I get lost. Does anyone have any advice how to do this? There are three other departments who have to do the same thing and I would love to be able to make everyone’s life that little bit easier.

Here are some rules about creating the “employee scheduling report:”

The “original schedule” has these different shifts:
v early = 6:30 to 15:30
early, victoria, eve = 7:00 to 16:00
early late = 7:00 to 16:00
mid= 9:00 to 18:00
late = 10:00 to 19:00

These should be entered into the “Time in” and “Time out” columns. I have tried using a nested IF statement to convert the text into times and then split the columns into time in and time out but I can’t seem to get the syntax right.

Everyone has regular days off which may change depending on how busy we are. When it is someone’s day off, “YES” should be entered into the “Is Rest Day” column of the “Employee Schedule Report.” I have somewhat automated this by having another tab “Staff Information” and using index/ match to input when it is someones regular day off. When a staff member changes their day off, then this is just inputted manually as a “yes” on the “employee scheduling report.” This has caused a couple of issues as this means the index/match formula is overwritten and as the supervisor uses an old file as a template, they may not realise that the formula is missing. If it is read directly off the “Original Schedule” the this would not be an issue.

The other statuses are: absent, holiday, sick. These should be entered into the “Remarks” column of the “Employee Scheduling Report” so that the payroll system can interpret these as needed.

The “Emp No” coumn is needed for the payroll system to recognize the staff member and has been assigned by the HR department.

Thank you all for your help.

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[Pepe Le Mokko]

Administrative Note:

Hi and welcome
although we value privacy as much as anyone else, it could be important that members have a rough idea of your location. You might in the future post questions which are bound to your regional settings.
So, please update your profile to something more precise then "Oliver_Watkins" ( country will suffice, no need to be more precise).
Thank you for helping us to help you

[Oliver_watkins]

Oops, Sorry. I didn't notice that when registering. It should be fixed now.

Thanks once again for any help you can provide.

[Oliver_watkins]

I have almost cracked it!

I created a pivot table from the original schedule and drilled down on the grand total which created a table I could use to populate the employee schedule report. Using this I have managed to fill in the employee schedule report with nested ifs etc.

I can refresh the pivot table when a new schedule is copied and pasted into the original schedule tab, but how do I get the drilled down table to update when I refresh the pivot table?

Thanks everyone!