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.

Originally Posted by MrShorty

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

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

Originally Posted by MrShorty

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.

Originally Posted by MrShorty

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

Is it done yet?