Employee Schedules

CLOSED
Bids
18
Avg Bid (USD)
$153
Project Budget (USD)
$30 - $250

Project Description:
I have 20+ employees and they all work different hours on different days. I need to graphically see what happens when I move someone's hours on 1 or all of their days.

**I used Excel but it's fine if you prefer to use MS Access.**

1) The current chart (Employee Schedules tab) shows daily schedules on a time scale from 3a ET to 2A ET (24 Hours). I would like the chart to be adjusted so that it shows 12A ET to 11pm ET. E.g. On Monday's schedule, time listed from 12am-2am should be listed on Tuesday’s schedule.

See in the attached Worksheet on tab “Employee Schedules.”

2) I need 2 line charts that automatically reflects the number of people I have working during a given hour - one graph for Mon-Fri and one for Sat-Sun. This would be calculated by summing the number of people that work daily during a given hour and multiplying each point by 4. See the example below:

Weekdays (Mon-Fri) @ 10pm
=======================
Mon - 5
Tues - 6
Wed - 3
Thurs - 11
Fri - 8
===============
Weekly Total = 33
Monthly Total = 33 * 4 = 132 (I realize that some months have more than 4 weeks but this doesn't have to be exact.)

These charts need to update automatically if I change someone’s hours on any or all scheduled days.
See chart example in the attached Worksheet on tab “Employees Per Month M-F.”

3) I need 2 additional line charts that reflect the information from the two charts requested in Item 2 above, in addition to the number of assignments per hour per month. The assignment data is static and so it has to be manually input. The employee hours data should be automatic based on their schedules.

See "Assignments" tab in the attached Worksheet for the Assigment data.
See “Emp and Assig Per Month M-F” tab in the attached Worksheet.

4) I need 2 more charts similar to what I requested in Item 3 above. So this chart needs to show the number of employees per month for a given hour and the number of assignments per hour in a given month. In addition, I would like to color code bars or the areas between these two lines based on their value. There are three categories - high, medium and low. I need to be able to change the values of the categories on the fly.

Low (blue): <50
Medium (green): 50<x<100
High (red): >100

Example: Montly at 12AM ET, my company has 132 people working and 67 assignments. The diffference between the two is 65 (132-67). 65 is in the medium category because it is 50<65<100. The color I chose to associate with this is Green so the drop line between both lines should be colored green. Please note, I want the ability to change the range of the categories. So if I change the category values to:

Low (blue): <20
Medium (green): 20<x<40
High (red): >40

then the bar at 12AM ET (65 value) should now be red because it is greater than 40 (>40)

-- M-F and S-S should have different categories and I should be able to change both independently.
-- See "Assignments" tab in the attached Worksheet for the Assigment data.

Key Considerations:

-- Employee schedules are listed in local time - Eastern Time, Pacific Time and, Japan Time.

-- I have setup the chart the best way I could. Please design as you see fit. For example, it would be great if I could select an employee from a dropdown, modify their hours and have it be automatically reflected visually.

-- There should be charts For Mon-Fri and Sat-Sun.

Skills required:
Excel, Microsoft Access
Additional Files: Employee_Schedule_Project.XLS
About the employer:
Verified
Public Clarification Board
Bids are hidden by the project creator. Log in as the employer to view bids or to bid on this project.
You will not be able to bid on this project if you are not qualified in one of the job categories. To see your qualifications click here.


Hire kulhanekd
$ 150
in 4 days
$ 250
in 7 days
Hire mrbrave
$ 120
in 4 days
$ 135
in 7 days
$ 250
in 10 days
$ 250
in 10 days
$ 35
in 5 days
$ 75
in 3 days
$ 75
in 3 days
Hire arset
$ 200
in 1 days