Method 1. Quick - use conditional formatting
Using conditional formatting, we can force Excel to fill a cell with any selected color if it falls between the start and end of the stage by date. The easiest way to do this is to use the logical function And , which in this case checks the obligatory fulfillment of both conditions (January 5 is later than January 4 and earlier than January 8):
Projects are carried out at all levels of the company and may require the efforts of one person or all. Its duration can vary from one week to more than two years. Some projects are by district, while others exceed even functional or organizational limits; that is, they attract more organizations or nations.
The tools that have been developed over time are designed to ensure the success of the project; which means achieving the goal of the same in set time and covering a number of claims that were raised for its launch. Project management is the planning, programming, monitoring, control and implementation of project activities to achieve goals within a specified time frame.
An example of such a diagram can be taken
Method 2. Long, but familiar - use a diagram
So, we have a table listing the stages of the project, the start and end dates and the duration of each stage:
The task is to build standard means chart-calendar graph, as in the picture:
Not surprisingly, project management requires strong management skills to develop effectively. With this tool, you can. 
This way you will have more time to plan and better manage your team, area, company, entrepreneurship project or research to take your meetings to the next level.
Project management requires the discipline of planning, organizing, directing, and controlling resources to achieve one or more goals with a defined start and end that will result in positive change or value addition. Use this tool with clear advantages over other specialized programs in combination with cloud services or network locations to strengthen project management and take them to the next level, so we invite you to judge for yourself.
Let's go, step by step:
Select the source data for the chart - range A2:B13 and choose from the menuInsert - Diagram, type - Linear with accumulation:
On the Row tab, click the Add button , set the cursor in the field Values and select cells with stage durations ( C2:C13 ):
How to work with work schedule?
This article is a contribution from licensing in administration specialized in finance. Evaluate it yourself if you have succeeded. The graph is quite intuitive, although different tools, functions and formulas are used. The columns are the days of the week.
Rice. 1 Schedule items on a sheet. The first week of the schedule is the start, but once you scroll down you will find more weeks to complete the data. Each action has its own name and assigned color. By inserting the names in the appropriate cells, you add your schedule.
Click the Next button and on the third step of the Wizard on the tab Uncheck the legend Add legend. All - click Done . It should look something like this:
Do not be afraid - everything is going according to plan - you just need to "bring to mind" our diagram. To do this, click right click mouse on the vertical axis with the names of the stages and select in the context menu Axis Format :
Next to the table is a list of colors and related actions. You can enter them into the table and the corresponding color will appear automatically. You can only add as many categories as there are colors - if you want to add more, you will need to manually add additional conditional formatting - that will be later in this article.
Action Schedule
Figure 2 Activities in different colors. Drop-down lists are presented within the entire schedule. If you select any of the cells, you will see an arrow on the right side. The drop-down list only lists the category names assigned to the colors next to the schedule table.
On the Scale tab in the window that opens, put two "checkmarks" -Reverse order of categories and Intersection with the Y-axis in the maximum category. Click OK. Now let's get rid of the blue columns. Double-click on any of them and in the window that opens, select an invisible frame and a transparent fill. You should get the following:
Figure 3 Drop-down list with selectable values. To enter a schedule. Select it from the list or enter it manually, or copy the action from elsewhere in the schedule and paste it into a cell. Be sure to copy the cell from the schedule instead of the table because the cells next to the table are formatted otherwise. If you want to fill multiple cells at once because it will take a long time, it's better to copy it from another schedule cell, select the multiple cells you want to paste to, and just paste.
Looks like the truth already, right? It remains to correctly configure the range of data displayed on the chart. To do this, you need to find out the real contents of the cells from which the timeline begins and ends (yellow and green cells in the table). The fact is that Excel only displays the date in the cell as day-month-year, but actually stores any date in the cell as the number of days that have passed from 1.1.1900 to the current date. Select the yellow and green cells and, one by one, try to set the General Format for them (menu Format - Cells). It turns out 38350 and 38427, respectively. Let's add three more days to the end date - we get 38340. Remember these numbers.
How the schedule works and how to set it up
It is difficult to create a tool that will fully satisfy the needs of another person, so it is important to be able to customize it. In all cells inside the schedule, a convenient record of actions has been added. The items that appear on this number are assignments assigned to the colors next to the schedule. With this solution, the chances are that the user accidentally enters the wrong name in the schedule.
The drop-down list automatically completes the next step in the table, but you must write them next to each other. In the Data Validation window, select List from the drop-down list. This template uses a slightly more complex solution, a formula that automatically checks for new values in subsequent cells and, if so, takes them into account. This list is best placed with all cells checked where it should be visible. Figure 4 Insert a formula in the dropdown list.
It remains to right-click on the horizontal time axis and select Axis Format and enter these numbers on the tab Scale :
After clicking OK the diagram will take the required form:
It remains to "bring some shine" - adjust colors, fonts, axis labels, etc. - with this, I think, you can handle it without my advice ... :)
If the action lasts, for example, 1 hour, then it must be entered in 4 cells. The easiest way is to copy the value from one cell to another. When populating multiple cells on a given day, only the first one retains the visible name of the activity. This prevents unnecessary information that obscures graphic image. This is related to conditional formatting, which you read more about in the following paragraphs.
Make sure you first save the activity you want to schedule next to the table, with the color you want. Each subsequent poem is 15 minutes later. You can change this device to something else, but if you choose too small, like 1 minute, a small part of the day will fit the schedule. Then, in the entire chart, for all entered weeks, the unit of measurement will change. Figure 5 Inputs in the second sheet.
FORUM NEWS  Aether Theory Knights | 11/18/2017 - 23:27: -> - Karim_Khaidarov. 10/19/2017 - 04:24: -> - Karim_Khaidarov. 10/11/2017 - 05:10: -> - Karim_Khaidarov. 05.10.2017 - 11:03: -> - Karim_Khaidarov. 04.10.2017 - 15:26: |
Almost all people perceive information “drawn” rather than “listened to” better. And even better, if this information is presented in images, and not a series of numbers and indicators. Imagine that a stranger is talking about his dog. He does not describe her appearance and pedigree, does not specify her color and age, etc. The imagination of each listener will draw its own image. And when we already imagine a handsome dog, it turns out that we were told about a lovely pug. In this situation, we will laugh, but faced with something like this at the enterprise, it will no longer be a laughing matter.
You can schedule a schedule for selected weeks. Go to the second sheet and change the start date to your choice - it will appear at the beginning of the chart. The next weeks will be automatically calculated using a simple formula. Colors are automatically inserted into cells using conditional formatting. This is based on the legend next to the table. If you go to conditional formatting and open the manager, you will see the rules assigned to the cells.
One rule is assigned to one color, each is assigned a formula. The first formula checks if the given cell contains the word assigned to this color next to the schedule - enough in this place. If you leave only one rule, then each cell will see the name of the action, and we want it to be visible only in the first one. To do this, we insert another rule that checks if the given cell contains the corresponding word, and if the cell above it also contains the word - if so, this means that the cell is not the first one.
Therefore, in production, everyone is trying to visualize as much as possible. One of the most important documents, especially in construction, is the work schedule. We can safely say that the entire project without this schedule is a waste of time. Since it contains all the accepted engineering and technical solutions, as well as optimized deadlines.
Then its contents must be hidden - the format that is assigned, in addition to the blue color, also contains the data format ";", that is, it completely hides the contents of the cell. Thus, two rules were created for each activity. If you want to use more of them, you must add them yourself using the method described above.
In addition, conditional formatting has been added, which draws a dotted line every hour, so working with the schedule is easier. Each column contains a formula that summarizes working hours. This value is multiplied by the base unit and then divided by the number of minutes in hours and the number of hours in a day. In this way, a value is calculated corresponding to the number of hours occupied by various activities.
What is a calendar plan?
The very title of this document gives an idea of its importance and significance. Calendar chart production of works is a table that displays all their volume and deadlines. In addition, the graph clearly shows the sequence of work performed, tied to specific dates (or simply the duration of the execution of various types of work - for standard projects). Most often, this document also contains information about the resources required at each stage of construction: basic materials, equipment and personnel.
In fact, there is a simple formula in these cells that is subtracted from normal working hours. This means that if someone works less than 8 hours, an orange bar appears on the left. If someone has worked on the measurement, then there is a green bar pointing to the right. If you want to continue the schedule for the next weeks, you need to copy the table from the row where the data bars are located in the row where you are last interested.
In addition, conditional formatting has been added, which draws a dotted line every hour, so working with the schedule is easier. Each column contains a formula that summarizes working hours. This value is multiplied by the base unit and then divided by the number of minutes in hours and the number of hours in a day. In this way, a value is calculated corresponding to the number of hours occupied by various activities.
The ability to draw up a work schedule is one of the most important skills of managers of different levels. The more accurate and detailed the schedule is, the better all the planned work will be carried out. Despite the fact that the "native" industry of the work schedule is considered to be construction, the leaders of all areas will not interfere with knowledge of the principles
Where to begin
Any job can be broken down into small tasks. The simplest example is the preparation of a fresh vegetable salad. It would seem that it is easier? But even this elementary task can be broken down into a sequence of actions. First, purchase all the ingredients, then wash them, cut and mix, season with sauce. Moreover, all actions can be broken in time (breaks in work appear), or they can be done sequentially, without breaks in time. In addition, all this can be done by one person, or maybe a whole team of cooks. So, there is a sequence of actions. It remains to calculate the time for each stage and determine how many and what kind of personnel is needed for this work. And the production schedule is almost ready for us.
Regardless of the industry, when planning, you first need to highlight the scope of work: break the whole process into components. Moreover, the criteria can be not only technological differences, but also the number of employees, and the necessary mechanisms and devices, etc.
Deadlines
After dividing everything into a sequence of actions, you can begin to calculate the timing of the work. For production and construction, there are norms and standards according to which specific terms are calculated for a given amount of work. For mental work, it is impossible to calculate the timing of the work according to the formula. But a manager with extensive experience, who has information about his staff, can quite clearly set the time frame for solving the task.
Knowing the deadlines for each type of work, we can begin to determine the time required to complete the entire process. It should be remembered that some tasks can be solved in parallel, and technological breaks are also needed for certain processes. 
Resource calculation
Certainly the staff essential element process. The schedule for the production of works in construction involves determining the number of performers, the specialization of workers and their qualifications. At this stage, we calculate the number and composition of teams and draw up a calendar plan for their employment at the facility.
Next, we turn to the definition necessary equipment, mechanisms and devices. In manufacturing industries, there are also regulations for this. And, finally, last but not least, this is the calculation of the materials necessary for the work.
Calculation of the delivery time of materials
All this information will allow you to combine the work schedule with the schedule for the supply of materials and equipment. Uniformity and continuity - these are the two main principles of planning. Optimizing the schedule in the direction of reducing the time may not give the desired result, because the work will be downtime due to lack of materials (or, conversely, the construction site will be literally clogged with them, and therefore it will take a lot of time to find the right one at the moment).
Force majeure increases the timing of work
Another important detail is that when drawing up a work plan, it is necessary to foresee possible risks. For construction, this can be anything from bad weather to heavy traffic on the roads. Given the force majeure circumstances, it is necessary to slightly increase the timing of certain types of work. Most often, this also affects the duration of the execution of their entire volume.
Despite this, planners should not chase after minimizing time. After all, when the production of work is disrupted, the general contractor will have to pay a penalty to both the customer and related contractors.
Graph automation
A few years ago, the calendar plan was drawn up manually. Specialists calculated all the deadlines and the need for personnel and materials, and then visualized it with the help. For small volumes work, it's an easy task. Another thing is if we are talking about a serious contracting organization that manages several objects at the same time.
Programmers are developing many auxiliary programs designed to automatically calculate and build a work schedule. Sample schedule calculated using Microsoft Office Project 2010 Professional, for example, is easily found on the Internet. However, not every company will agree to spend additional funds on installation software and training staff to work with it. In addition, each specialized program has its drawbacks. One does not take into account the possibility of shift work, the other without writing macros is not consistent with the calculation of materials, for example, etc.
Therefore, most planners have learned to build a work schedule in Excel.
This program has many advantages:
- It's free. In the sense that Excel is part of the standard MS Office package, which is unconditionally installed on almost every computer.
- She is simple. Having minimal knowledge about calculating formulas and linking sheets to each other, you can do planning.
- She is visual. All calculations and the result are displayed on one sheet. And the changes are immediately displayed on the chart.