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.
-- 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.
17 freelancers are bidding on average $151 for this job
Hi, I can do it! I'm programmer for years and very familiar with VBA for excel automation. What is your excel version? Looking forwards to work with you, Cuong NH
Experienced in Access. Electrical Engineering graduate. Start doing now. Hope to give you the product ASAP. Really want to take the job. Please give a chance to new bidders.
Hello, I used to made same type schedule for Bulgarian hotels & restaurants activity. I believe, that I have an example of you valid table could easily made the asked one. Best regards, A. Setian