• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help in Generating a 24 hours Job Distribution Chart

N

NiCKz

New Member
Hi All,

I have a Job Distribution sheet, and I need to automatically generate a chart for different jobs distributed over 24 hours in day. Different Jobs are executed on different days of the week based on their frequency. Details below:
Attached is an excel file with sample data.

'Job Schedule' sheet:
Job Status = Only Active jobs are scheduled and executed
Job Frequency = No. of Days in a week it is scheduled
Job Schedule = What time it is scheduled
Job Duration = Total Job Execution Time in minutes

'Time Distribution' sheet:
I created this manually just for Sunday. It shows the 24 hour job distribution on Sunday.
Each cell is 15 minutes.
Total duration marked here is Job scheduled time + job duration.
B3-CS3 shows me overall time distribution (just like Outlook meeting scheduler), so I get to know which time slots are busy and which are free.

Please help me in automating this chart. Thanks..

Currently, if I change any schedule or add any new job, I'll have to change the chart manually for all days (7 sheets - 1 chart for each day), which would not be feasible.

Can I just have one chart and have a drop-down for day, and then when I select it, the data in the chart automatically changes based on the day?
 

Attachments

  • Job_Schedule_Chart.xlsx
    18.8 KB · Views: 5
See if this does what you want?
Job Schedule A2 has a drop down in it
 

Attachments

  • Job_Schedule_Chart-Hui.xlsx
    38.3 KB · Views: 9
Hi Hui.. This is awesome! :):awesome:

Few queries:
  1. When I change the day from drop-down, B3:CS3 is not updated. Here, I would like to display the overall time distribution of all jobs over 24 hrs (just like Outlook calendar meeting scheduler), so I get to know which time slots are occupied and which are free. I did it manually for Sunday previously.
  2. What are the values in cells CP1 and CQ1 used for? or it's just dummy data?
  3. Can I drag the formula at cell B4 through CS20 (both vertically and horizontally) without any modifications?
I need to do this in my real scenario (client excel sheet). Could you please help me understand the logic and steps to do it, this will help me learn as well.
Thanks a lot for a super-fast answer! :)

Thanks,
Nick
 
1. I wondered what they were
In B3: =COUNTIFS(B$4:B$20,TRUE)
Copy across
Then set a CF to suit
2. Whoops, Nothing, I forgot to delete those cells.
3. Yes, There is basic Conditional Formatting applied to those cells also

see attached file:
 

Attachments

  • Job_Schedule_Chart-Hui2.xlsx
    38.7 KB · Views: 9
Thank you so much Hui.. I successfully implemented this! :)

I was wondering if the same thing can be implemented using Excel Charts (Gantt Charts)?
- A drop-down above the chart for days.
- Based on the selection of day, the chart will be generated/refreshed
X-Axis: 24 hours (Major: 1 hour, Minor: 15 mins)
Y-Axis: Job ID/Name

With this,
1) the look and feel of the chart will be good,
2) users wouldn't have to change the sheet every time a new job is added to the 'Job Schedule' sheet (that would mean adding another row and copying formula and CF to the 'Time Distribution' sheet as well), and
3) no need to worry about the formatting and formula in the second sheet 'Time Distribution'.

Is this possible? Can this be done using the same table in 1st sheet or a new table is required to fetch data for chart's X and Y axis?
 
In regards to Points 2 & 3
The Simple way is to insert a Row before the last row of the Time Distribution worksheet
Copy the row above down
Change the Name of the Item in Column A
That way the formulas and CF come down correctly

You can use a Chart but it's formatting isn't as good as can be done on the worksheet
 
Last edited:
Ahh.. so it is possible! True, your first solution is more geared towards what I want to achieve. The worksheet gives a much better view, and additionally lets users know the free and occupied times for a day, which is very helpful when scheduling new jobs.

I am just concerned about other users not following the proper way of addition of a new row in both worksheets, which might cause a mismatch.

Can we automatically pull the Job IDs from Column A in 'Job Schedule' sheet to Column A in 'Time Distribution' sheet - only those Job IDs with Job status 'Enabled'?
This way I can lock all cells in the 2nd sheet except the Weekday drop-down list. Users would then just have to enter a new job or disable an existing job in the first sheet.
 
I have rationalised the logic of the data flow and checks which has simplified the formulas on the Gannt Chart sheet

This means that you can add rows to the first sheet and the second sheet will update or be easy to update

To add data Insert a Row on the Job Schedule worksheet after the last row
Copy the entire Last Row with data above the new row down

On the Time Distribution worksheet, Copy the last row with any data down

Toggle days and Enabled/Disabled etc and see how the chart changes

Voila
 

Attachments

  • Job_Schedule_Chart-Hui3.xlsx
    58.1 KB · Views: 18
Last edited:
No Please do let them know
Please let us know in the future if your cross-posting
 
You must log in or register to reply here.
Back
Top