+ Reply to Thread
Results 1 to 12 of 12

Schedule graph to show how many employees will be active

  1. #1
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9

    Schedule graph to show how many employees will be active

    I have created a new master schedule for my job and want to see if it gets approved for me to implement it. They are requesting I present it with graph to show how many active employees I will have throughout the day.

    The schedule is Monday - Sunday and have 2 columns in each day with start time and end time. For example: I have 6 employees starting at 2am and ending at 11am, then I have 4 employees starting at 2:30am and ending at 11:30am that means that at 2:30am I will have a total of 10 active employees. Then at 10am I have 11 employees starting and ending at 8:30pm so at 10am I will have 21 active employees then at 11am I will 15 active employees since my 2am will be ending their shift, then at 11:30am I will have 11 active employees since the 2:30am are ending their shift.

    I would like to creat a graph that would show me how many active employees I will have throughout the day.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Schedule graph to show how many employees will be active

    Charts don't analyze data, so my question is -- do you need help generating the summary data or do you need help creating the chart from summary data you've already computed? What I would expect to see in the spreadsheet:

    1) COUNTIFS(), SUMPRODUCT(), or similar functions or maybe a pivot table that is counting how many employees are active at each time of interest.
    2) A line, column, bar, or scatter chart based on the columns/rows containing those functions that will visualize the data.

    Which part do you need help with?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9
    Quote Originally Posted by MrShorty View Post
    Charts don't analyze data, so my question is -- do you need help generating the summary data or do you need help creating the chart from summary data you've already computed? What I would expect to see in the spreadsheet:

    1) COUNTIFS(), SUMPRODUCT(), or similar functions or maybe a pivot table that is counting how many employees are active at each time of interest.
    2) A line, column, bar, or scatter chart based on the columns/rows containing those functions that will visualize the data.

    Which part do you need help with?


    Hello and thank you for your reply. I would need help creating the chart from data I already computed. I will upload the spreadsheet tomorrow so you can see what kind of data I'm taking about

  4. #4
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9

    Re: Schedule graph to show how many employees will be active

    I tried to upload the excel that I would be using but it did not let me. So I took a screenshot of it so that you can have an idea of what I am talking about. This is schedules of drivers that will be on the road at a certain time and then will be coming back a certain time and would like to create a graph that would show how many drivers will on the road at any certain time of the day. SO that I can show the resources that I will be using Attachment 646169Attachment 646169

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Schedule graph to show how many employees will be active

    The only reliable way to upload a file/screenshot is to:
    1) Click "reply to thread" or "go advanced" to open the full featured post editor.
    2) Beneath the edit window should be a link to "manage attachments" Click on this link to open the site's file uploader.
    3) Follow instructions to attach a file to your post.

  6. #6
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9

    Re: Schedule graph to show how many employees will be active

    I hope the file is attached.


    This is how my proposed schedule looks. I would like to show a graph that can sort of look like the picture I attached that would show
    the number of drivers or resources that I will have actively working throughout the day. So the graph will sort
    of be adjusted for employees ending their shift with new employees starting their shift
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by sirbonesalot1; 10-21-2019 at 04:53 PM.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Schedule graph to show how many employees will be active

    I cannot tell what is the summary data that you want to include in the chart. Your picture shows an area chart with numbers (2 digit years??) across the horizontal axis and values on the vertical axis. I'm having trouble understanding how the chart elements in your picture correspond to what is in your spreadsheet.

    When creating charts in a spreadsheet, you start by arranging the desired data in the spreadsheet. Usually you put your "horizontal category x axis" data in one column (row), then put the summary data (y values) in the adjacent column (row). Then select those 2 columns (rows) and insert the chart.

    Help us recognize what data should be included in the chart, and we should be able to help you create the chart.

  8. #8
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9
    Quote Originally Posted by MrShorty View Post
    I cannot tell what is the summary data that you want to include in the chart. Your picture shows an area chart with numbers (2 digit years??) across the horizontal axis and values on the vertical axis. I'm having trouble understanding how the chart elements in your picture correspond to what is in your spreadsheet.

    When creating charts in a spreadsheet, you start by arranging the desired data in the spreadsheet. Usually you put your "horizontal category x axis" data in one column (row), then put the summary data (y values) in the adjacent column (row). Then select those 2 columns (rows) and insert the chart.

    Help us recognize what data should be included in the chart, and we should be able to help you create the chart.

    Sorry that was just an example of what I was looking for. I would like to show the hours on the right side of the graph and the day on the bottom. I want to be able to show how many active drivers I will have at any given time of the day for the entire week day by day. On the excel sheet I have 2 column per day a starting time and ending time. I would like to be able to point out how many active employees I would have at any time of the day and show that my new propose schedule makes the best use of resources we have...

    So the picture I attached was just an example of the kind of graph I'm looking for. I would say that I might need 7 didn't graph to be able to show all 7 days of the week.

    I hope I was able to explained it.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Schedule graph to show how many employees will be active

    You talk about having your chart show "count of employees" at different times of day, but I don't see anywhere where you have counted the data (post #3 implied to me that you had already counted the data). As I indicated in my first reply, we have to have the data in the spreadsheet before we can create a chart from the data. Here's what I would have expected (I don't understand exactly what I am looking at in your file, so it will probably need adaptation, but it should provide a starting point for both generating the summary data and then creating the chart from that summary data):

    0) I'm assuming that the data you have is showing individual employee start/stop time of day for each weekday. Adapt to whatever the data really mean. If I understand what you are trying to do, I would have also expected a chart with time of day along the x axis, count of employees on the y axis, and then show individual days of the week as separate data series. That's not exactly what you are describing, but it should work for explaining the process of summarizing data and creating a chart from the summary data. We should be able to adapt the process to the specific chart and axes you want if we get the overall process understood.
    1) I need a summary table with time of day down one side of the table, day of the week across the top of the table, and count of employees in the body of the table. To build the summary table, I:
    1a) Add a column of time of day. Enter 0:00 in X4, 1:00 in X5, 2:00, in X6, then fill down to 23:00 in X27
    1b) Add a row with day of the week at the top. Enter Mon in Y3, Tue in Z3, and so on across the top.
    1c) A formula to count the number of employees at each hour of the day for each day of the week. Enter =COUNTIFS($K$3:$K$300,"<="&$X4,$L$3:$L$300,">="&$X4) into Y4. Copy down to get the counts of column K and L that fit the criteria. Similar for each day of the week across the table. Now I should have a table of how many employees are working each hour of the day.
    2) Now to create the chart. I would probably use a line or scatter chart for this, but a column or area chart could work as well. Select the table (X3:AE23) and insert a line chart. Excel should put time of day as the horizontal x axis and count of employees on the vertical y axis. There should be seven data series -- one for each day of the week.

    If I have interpreted the raw data in your sample sheet correctly, that should generate a chart that gives an indication of how many employees are working at each hour of the day. It's not exactly the chart you describe (day of week on the horizontal axis, time of day on vertical axis, count of employees somehow indicated at the data marker), but maybe it will be good enough. If not, help us understand what you want to do differently, and we'll help as best we can.

  10. #10
    Registered User
    Join Date
    06-13-2019
    Location
    Los Angeles, California
    MS-Off Ver
    2010
    Posts
    9

    Re: Schedule graph to show how many employees will be active

    Quote Originally Posted by MrShorty View Post
    You talk about having your chart show "count of employees" at different times of day, but I don't see anywhere where you have counted the data (post #3 implied to me that you had already counted the data). As I indicated in my first reply, we have to have the data in the spreadsheet before we can create a chart from the data. Here's what I would have expected (I don't understand exactly what I am looking at in your file, so it will probably need adaptation, but it should provide a starting point for both generating the summary data and then creating the chart from that summary data):

    0) I'm assuming that the data you have is showing individual employee start/stop time of day for each weekday. Adapt to whatever the data really mean. If I understand what you are trying to do, I would have also expected a chart with time of day along the x axis, count of employees on the y axis, and then show individual days of the week as separate data series. That's not exactly what you are describing, but it should work for explaining the process of summarizing data and creating a chart from the summary data. We should be able to adapt the process to the specific chart and axes you want if we get the overall process understood.
    1) I need a summary table with time of day down one side of the table, day of the week across the top of the table, and count of employees in the body of the table. To build the summary table, I:
    1a) Add a column of time of day. Enter 0:00 in X4, 1:00 in X5, 2:00, in X6, then fill down to 23:00 in X27
    1b) Add a row with day of the week at the top. Enter Mon in Y3, Tue in Z3, and so on across the top.
    1c) A formula to count the number of employees at each hour of the day for each day of the week. Enter =COUNTIFS($K$3:$K$300,"<="&$X4,$L$3:$L$300,">="&$X4) into Y4. Copy down to get the counts of column K and L that fit the criteria. Similar for each day of the week across the table. Now I should have a table of how many employees are working each hour of the day.
    2) Now to create the chart. I would probably use a line or scatter chart for this, but a column or area chart could work as well. Select the table (X3:AE23) and insert a line chart. Excel should put time of day as the horizontal x axis and count of employees on the vertical y axis. There should be seven data series -- one for each day of the week.

    If I have interpreted the raw data in your sample sheet correctly, that should generate a chart that gives an indication of how many employees are working at each hour of the day. It's not exactly the chart you describe (day of week on the horizontal axis, time of day on vertical axis, count of employees somehow indicated at the data marker), but maybe it will be good enough. If not, help us understand what you want to do differently, and we'll help as best we can.
    AHHHHH i see what you mean. I will do that and try it and let you know how it comes out. Thank you for the update

  11. #11
    Registered User
    Join Date
    08-25-2023
    Location
    USA
    MS-Off Ver
    2302
    Posts
    1

    Re: Schedule graph to show how many employees will be active

    Is it done yet?

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,330

    Re: Schedule graph to show how many employees will be active

    Is what done yet? This thread is four years old ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How can I use Solver to schedule my employees (Healthcare)?
    By lsm33000 in forum Excel General
    Replies: 11
    Last Post: 11-18-2020, 07:11 PM
  2. [SOLVED] Daily Schedule for 8 Employees
    By Rigged in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-28-2017, 09:46 PM
  3. Forecast How Many Employees To Hire Based on Active and Termed Employees
    By Kanook22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2014, 01:19 PM
  4. Making a Job Flow/Schedule for Employees
    By flareindustries in forum Excel General
    Replies: 1
    Last Post: 04-04-2014, 10:20 AM
  5. Group Employees to a shift schedule
    By aka 42 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2013, 10:25 PM
  6. [SOLVED] Employees shift schedule
    By rossi in forum Excel General
    Replies: 1
    Last Post: 06-04-2006, 07:15 PM
  7. [SOLVED] How can i set up work schedule for 5 employees
    By help me in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-10-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1