Complex Rota

[ruthtwigg]

Hi, I hope someone can help, I've been scratching my head for hours.

I'm trying to create a spreadsheet to replace our current paper rota, as attached, but am struggling to find a way to do it that will convince my extremely technophobic colleagues that it'll make their lives easier.

Essentially we have a number of staff, all of whom work different hours, and in different areas within the department - for example person 1 might work in Theatre on a Monday from 9-5 and then in Clinic on a Tuesday from 7 - 2, or they might split their day and work in 2 areas within the same working day. I'd like to be able to automatically calculate how many hours they have done per day (adjusted for lunch breaks) and measure that against their contracted hours for the week.

I'd originally put 2 columns for each day - with start time in one, and finish in the other with the aim of calculating the hours between these 2 times, but I couldn't figure out how to adjust it for lunch breaks (especially as some people only work half days and so don't need lunch breaks removing). I then started doing it with a drop down list, but as each person also has 2 rows (one for area and one for hours worked), it was getting extremely complicated and I thought other members of staff would likely have difficulty with it in my absence.

Any advice or ideas would be greatly appreciated.

offduty.jpg

[dflak]

Who fills this sheet in? Is it filled in daily or weekly? Can you elaborate on the process a bit, perhaps walking through an example or two.

[ruthtwigg]

It's filled in every 6 weeks or so, by one of three different line managers. The image attached illustrates it better, but essentially it needs to show

The date & day along the top and then for each date:

Person - Area of work AM/PM - start and finish time for that day - (repeated for each day) - total hours worked for that week (with lunch breaks deducted)

[dflak]

Attach a sample workbook. I cannot read the scrawls in the picture. What I understand is that you have people in the columns and dates in the rows and times at the intersections? The only way this would work is to have the start and end time in the same cell. This is not a good design. How does the manager collect the times?

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.