Automated Shift work roster

Cancelled Posted Aug 27, 2008 Paid on delivery
Cancelled Paid on delivery

Guys I need someone who understands VBA and excel here. Cheap and fast..

Looking to automate a rotating line roster with weekly and daily roster sheets.

I have right now excel spread sheet which functions manually and also a half done VBA excel one. Ill be very specific here in what i need - deadline is in 4 days, running excel 2002 on win 2000 machines:

Features:

Weekly roster:

- As already existing you change the 1st day of the week (Wednesdays) date from eg 20/08/08 to 27/08/08 it will then change all dates for that week, I need it to also

o Change Roster shifts eg “line number” represents the shift for that week- that never changes. The only thing that changes is that next week someone else will be working that line (everyone drops down a line each week) hence when changing dates for that week occurs so should the names drop down to work a new line. Also at this time the Leave sheet, Overtime and payroll sheet for that past week should be generated (with a option to generate them again at anytime)

o Also to be generated when the date is changed as per above is the daily work sheets for the upcoming week – These sheets should (as all others) be printable and reflect changes in the weekly roster eg: today is wed and I generate all the above sheets but on wed 2 people call in sick so I input that change in the weekly roster and it updates Thursday on the daily worksheet (so that daily work sheets are always live)

o Current line number and names on them must be kept ie you can just put all names in alphabetical order and have line numbers with new names

o Weekly roster should have a easy way to add a new roster line and staff member whilst keeping the formulas and everything attached to that.

o Weekly roster as per now should keep all names in a drop list for shift swaps – currently those shift swaps dont include break finish times – I need the shift swaps to reflect break start and finish time

o Another drop down box exists for leave types this can be part of the shift swaps drop box ie under names there is sick/annual/etc (ill get the rest to you) or it can be another format you think better. This generates to the “Leave” form

o Weekly roster has both breaks (as now)

o Print out of weekly roster should include staff name/line number/shifts and breaks

o All weekly timesheets should be generated with the starting week date as title and same for Overtime/Daily/Payroll and leave sheets.

o SO That sheet would generate

-a printable weekly roster with breaks as is does now (or could it be the same roster)

- -A daily work sheet

- A weekly payroll sheet (simply weekly roster without break times – showing scheduled or shift swap shifts but not O/T

- A weekly overtime and Leave for (which i have currently manually done but havent sent you)

Daily work Sheets:

- Visually pretty much the same as now with “Tasks” remaining a manual or static element. Daily work sheets should be generated as stated above “when the new week begins” but also need to be able to be created at any time?

o Any daily or last minute changes such as sick days or late start/early finish, overtime, shift changes etc in the “weekly roster” through out the existing week should reflect in the daily work sheets

o Daily worksheet has both breaks (as currently) for fulltimers and one break for partimers and none for Supervisors

o daily work sheet generated that i can print daily (showing everyone on that roster for the day, and breaks)

Leave form:

- Requires 2 new columns “Leave form received” which would be a tick box or something like that and that would be a manual process, and “Action” which would be maybe a drop down box? With “approved” or denied”

- “Leave Type” column needs the submenu’s of “

• Annual Leave

Unpaid Leave

Long Service Leave

Sick Leave

Careers Leave

Bereavement Leave

Twentieth Day

DIL

Other

- When I enter the leave type the name/dates/time and SELECT “approved” will immediately go the payroll sheet for the relevant weeks – (is this possible with annual leave which may be requested 2 months before? ie will it store this info till the relevant time? AND reflect in the weekly and daily roster

- “hours absent” defaults to 0 hrs

- Default “actual start and actual finish times” are auto generated as a dash “-“ but you can manually add for example 2 hrs late arrival as say 9am and 11am finish which generates onto the payroll sheet.

- staff numbers are automatically generated when you put the name in?

-

- Also all leave is unactioned until a leave form is completed. So with that leave form and the check box i included to add whats the Possibility of pulling a report on all unactioned leave- A weekly and monthly report if possible and it only needs to be on unactioned (By unactioned I mean leave that is requested

Overtime sheet

- Will be manually filled but automatically generated weekly with the others. Only thing that will change when this is generated weekly is the date.

-

Payroll sheet

- Payroll sheet should be generated with a start day of wed and the fin date of a tues, if its possible to have some kind of push button at the top left of every weekly roster which then generate a sheet marked "payroll and the date" that would be a good. Payroll will be just a version of the “weekly roster” without the breaks.

-

Lastly Archive in pdf of daily and weekly sheets? Or whatever you think is best here.

I can send existing files tonight

Excel Visual Basic

Project ID: #306406

About the project

8 proposals Remote project Active Sep 25, 2008