Office

Monthly schedule excel template. Creating a Calendar in Microsoft Excel

Description

The presented schedule allows you to distribute the workforce throughout the month and day so as to obtain an equal workload of the department throughout the entire period of its operation.

Please note that the option under consideration is not canonical and may require additions for specific business tasks. But it can also be used as a turnkey solution and has been tested in one of Russian companies, where the effectiveness of its implementation significantly exceeded the costs.

The purpose of this article is to demonstrate the transition from intuitive personnel planning to more meaningful ones.

Structure

The schedule file consists of one sheet, which contains:

A list of employees;
Schedule;
Information table and graphs.

A list of employees

The list of employees begins as a regular list and ends with the phrase “end of list.” This is done specifically so that when adding a new employee, you do not accidentally lose the correct range of formulas in the information table.

Incl. when adding new line For an employee, highlight the line with the phrase “end of list.”

Schedule

Employees' work shifts are determined by hyphenated hours, such as "9-18." Hours are indicated in 24 hour time format and may be preceded by a zero, i.e. shifts “7-16”, “07-16” and “07-016” are equivalent, although it is recommended to use the first option, because it is the most read.

The schedule is set for each employee for a specific day, i.e. if you need to specify a night shift, for example from 21.00 to 9.00 the next day, then in one day you need to indicate “21-00”, and in the next “00-09”.

If the shifts are specified in the wrong order, this will result in incorrect counts in the information table and will not necessarily cause an error. Example of incorrect shift order "15-07".

Information table

The information table displays the results for each day and for each hour of the day and contains the following information:

“Working employees” – shows maximum amount simultaneously working employees per day or per hour;
"Prod. per employee” – shows the average productivity per employee per day (for the day) and the average productivity per employee in a specific hour. Expressed in quantity (clients, calls, questionnaires, letters, etc.). Productivity per employee per hour is set manually, based on specific department statistics;
« Overall Performance» – shows the sum of the productivity of all employees;
“Load forecast” – shows the load forecast for each day and hour. The forecast is carried out manually, based on specific department statistics, and is expressed in quantity (clients, calls, questionnaires, letters, etc.);
“Load rate” – expressed as a percentage and indicated manually for each hour. Shows how busy the department must be so that work is completed without failures, delays and overtime;
“Loading” – shows the workload of the department with the current schedule or displays the error “err”;
“Deviation norm” – is set as a percentage and indicates the permissible deviation from the loading norm. The Load line fields have conditional formatting to help you track problem areas in your schedule. For example, if the load is within the load deviation standard, the cell is highlighted in green. If the load exceeds the deviation norm upward or an error is generated, the cell is highlighted in red. If the load exceeds the downward deviation norm, then the cell is highlighted in blue.

The available graphs (two for each day) clearly demonstrate the coverage of the load on the department by its productivity and the deviation from the workload norm for each hour of the day.

To better understand the graphs and set all parameters, use the tips at the end of this article.

1. What is the minimum number of employees required to work simultaneously?

This is necessary because if one of the employees loses temporary ability to work, then there will be no need to urgently select a replacement.

2. How to correctly determine employee productivity?

Employee productivity can be measured different ways. It all depends on the priority of the goals.

If you need to cover the load on a department as accurately as possible, then it is better to determine personal productivity for each employee, because An experienced employee and a trainee will have different performance levels. If you want to use this approach, then unmerge the cells containing the full names of employees and enter a quantitative indicator of employee productivity in the free column. Then, in the information table, in the "Overall Productivity" row, replace the formula by using the SUMIF() function using the employee performance table.

The approach described above allows you to see most closely how the department will work, but it has its drawbacks: the need to create additional statistics for analysis; the difficulty of monitoring and determining the causes of failures in the efficiency of the department; this method still does not allow you to accurately determine an employee’s performance depending on different situations:

the employee works after vacation;
work efficiency before and after the lunch break can vary greatly;
efficiency varies depending on load type (customers with various questions, different complexity of non-uniform tasks, etc.).

A simpler way to measure productivity is the department's average productivity over the previous period. If full coverage of the incoming load is not so important and allows for deviations, then it is better to use a productivity standard to which all employees should strive. When planning your schedule, allow for a small productivity reduction factor for interns, furloughed employees, etc. Also remember that productivity may vary at different times of the day, and that employees take breaks.

Using the latter method simplifies control, because The productivity standard is determined for everyone and, in case of deviations, work with employees occurs individually.

3. How to determine the loading rate and deviation rate?

The loading rate is often difficult to determine immediately; often this can only be done empirically using different kinds statistics and analysis, outside observations, personal surveys, etc. Also, for each individual type, the loading rate is unique, but there are points to which we want to draw your attention:

Do not use a loading rate of 100%, because... such workload puts enormous stress on employees, which will ultimately lead to “burnout at work” and, accordingly, an increase in staff turnover;
Keep in mind that the load may not arrive gradually, but in “waves”. For example, a department load of 20 clients in a particular hour could mean that the first 5 arrive in the first 30 minutes, and the next in the last 30 minutes, creating a wait that could continue until the end of the hour, or carry on to subsequent hours, increasing their load , and some clients may want to contact you later;
Determine experimentally under what loads the performance indicators of the department do not change much and correspond to the desired parameters, thus you will receive a range of load values, where the average of this range will be the norm, and the rest of the same range will be an acceptable deviation.

Download schedule

Forward >

If the site materials helped you, please support the project so that I can develop it further.

On our website you can download, fill out and print a school schedule template ( lesson schedule to fill out). For your convenience, we have prepared several options, such as page and landscape options, in Word files and Excel (Word and Excel).

Bell schedule for 45 minute lessons.

If your school has a different duration of breaks, you can always edit the downloaded file.

The schedule changes throughout the year, so it is very convenient to fill out our templates with your data and print them on a regular A4 sheet. We recommend adding our site to your favorites.

Schedule Word template No. 1 (minimalistic, page-based)

Text file of the lesson schedule in a table, in Word format from Monday to Friday, 7 lessons. A4 page sheet, black and white.

Schedule Word template No. 2 (indicating the time of lessons and breaks). Page.

Text file of the lesson schedule in a table, in Word format from Monday to Friday, 7 lessons. Indicating the time of lessons and breaks. A4 page sheet, black and white.

Schedule Word template No. 3 (indicating the time of lessons and breaks). Landscape.

Text file of the lesson schedule in a table, in Word format from Monday to Friday, 7 lessons. Indicating the time of lessons and breaks. A4 album sheet, black and white.

Lesson schedule Excel template No. 1. Page.

Lesson schedule file in a table, in Excel format from Monday to Friday, 7 lessons. Indicating the time of lessons and breaks. A4 page sheet, black and white.

And now it's time to move on to creating lists. Lists in Excel can be very different: in the form of lists you can draw up plans, schedules, plans for the future, product catalogs, contact details of partners, clients, or just acquaintances. You can store phone numbers, favorite sites, and book titles in the form of Excel lists.

Let's have a little practical work: Let’s draw up an abstract schedule for now. Write in the cell A1 word date, in a cell B1 word Day, in a cell C1: Lesson topic , in a cell D1: Teacher . Please note: some of the cell contents C1 And D1 does not fit within the boundaries of the cells, but crawls to the next cell. And the contents of the cell D1 overlaps what is entered in the cell C1. No problem: whatever is in the cell C1, You will still be able to see if you make it active: the content is displayed in formula bar (above the table). To make the content visible in the table, you need to expand column boundaries: to do this, move your mouse to the border between the column designations (between the letters C And D), and when the cursor changes to a double-sided arrow, press the left mouse button and move this border. Expand the column this way C and column D so that everything fits.

Continue filling out Excel spreadsheet . To cells A2 some date, for example, 01/14/13, and press on the keyboard Enter. Please note: the date has been transformed, the inscription now looks like this: 14.01.2013 . This is because the cell format has changed to date format. We'll look at the formats later. The most interesting thing is that you don’t need to fill in the remaining dates in the cells: make this cell active again, hover your mouse over the dot in the lower right corner of the frame, and when the cursor turns into a small cross, press the left mouse button and drag down. The dates appear below: 15.01.2013, 16.01.2013 and so on, ascending. Moreover, if you continue to pull further, you will find that after 31.01.2013 coming 01.02.2013 , that is, there is an automatic transition to another month. Thus, you don’t need to calculate how many days there are in a month, you don’t need to look at it - the program will do everything for you.

A logical question arises - what to do if you need the same date in all cells? This is done very simply: You write this date in two cells, then select both cells at once, and then drag the black dot in the lower right corner of the frame down. All dates will be the same.

Now write in the cells B2, B3, B4, B5, B6, B7, B8 respectively Mon, Tue, Wed, Thu, Fri, Sat, Sun . Select these cells all at once, then right-click and select Copy. Now you do active cell B9, right-click and select Insert .

In column C write the names of the lesson topics in the cells, and in the column D- surnames of teachers. Come up with something, I rely on your imagination. It is not necessary to fill out all the cells; do it selectively. You can copy the contents of cells and put them in new places: classes and teachers in the schedule can be repeated! For example, in cells C3 And D3 write: Mathematics, Ivanov . Select both of these cells at once, then right-click, select Copy, then select some cell in the column C, for example, cell C5, right click, select Insert .

The schedule is ready, and all that remains is to bring it into proper form. By the same principle and using the same operations they are made anylists in Excel: lists of sites, purchases, books, plans, addresses, partners, clients, acquaintances and so on.

More details You can get it in the “All Courses” and “Utilities” sections, which can be accessed through the top menu of the site. In these sections, articles are grouped by topic into blocks containing the most detailed (as far as possible) information on various topics.

You can also subscribe to the blog and learn about all new articles.
It does not take a lot of time. Just click on the link below:

Sections: School administration

This article presents the experience of scientific organization of work of the deputy director for educational work based on the use of information technology.

The description will be of interest and practical value for those who would like to use ICT in their work, but do not have the opportunity to purchase a ready-made workstation program for the head teacher. To organize an automated workstation (AWS) for the deputy director for educational work, the popular Office software package, in particular Excel, was used.

1. Working with the schedule

Creating a schedule is one of the labor-intensive tasks in the work of a head teacher. For many objective reasons, this task cannot be fully automated due to the installation of many conditions and restrictions, often contradicting each other. This is due to the lack of classrooms and insufficient number of teachers (in many cases artificially created, because if a teacher works 18 hours, his children will remain hungry) and overcrowding in classes, etc. Anyone who has ever made a schedule is familiar with these problems and knows that they are solved mainly not by the iron logic of mathematics, but by the logic of finding compromises and deviations from the requirements of the rules.

Based on the above, the task of fully automating scheduling was not set.

The program, based on Excel spreadsheets, allows you to partially automate the scheduling process and control the accuracy and quality of the schedule. Realizing that the volume of the article does not allow me to dwell in detail on the features of the Excel program, but wanting this article to be read not only by people who are fluent in computer technology, but also by those for whom it could become a real help in their work, guidance to action, I will briefly explain what “Excel” is.

The main document of this program is a workbook, consisting of sheets presented in the form of worksheets or chart sheets that can be copied, sorted, and transferred.

A workbook is the electronic equivalent of a folder. The name of each sheet appears on a label at the bottom of the workbook. A worksheet is a grid of rows and columns. Each cell is formed by the intersection of a row and a column and has its own unique address or link. For example, the cell located at the intersection of column B and row 5 has address B5. These addresses are used when recording both text information and mathematical and logical formulas or cell references.

Let's look at working with the schedule in this program.

A template for scheduling is created.

On the first sheet is a schedule form for teachers (see fragment schedule 1).

On the left are the names of teachers, subjects, workload, in the upper part are the days of the week, divided into lessons (each cell consists of two - for a class and an office.) We put down the classes, the office is filled in automatically, obeying the logical formula written in it, the essence of which can be express approximately as follows - if the cell located to the left is empty, then no recording is made, otherwise the value of the office assigned to this teacher, recorded in a separate column at the end of the schedule table, is substituted.

The schedule for students by class, which is located to the right on the same sheet, is filled out automatically, i.e. the name of the subject is inserted into the cell of the class indicated in the corresponding cell of the schedule for teachers (see schedule fragment 2). When filling out the schedule by teacher, the same class may be mistakenly recorded for several teachers. Only the subject that comes first will be included in the student schedule. To ensure timely detection of errors in the schedule, there is an additional line on which the number of lessons according to the teacher’s schedule is automatically calculated. If the number matches the number of lessons listed, then everything is correct. If the list of items has one quantity, but the number below shows another, then you need to look for an error. You can also provide an audible signal.

Thus, when filling out the schedule for students, I eliminate the routine work of rewriting the schedule, and later, when printing, this program allows you to print the schedule in different scales, based on the size of the stand, purpose, etc.

When you start working with the schedule in this program, it’s scary that you don’t see the entire schedule. When filling out the schedule manually, you have to run your eyes over the sheet. This program has a function for pinning areas, which allows you to see on the screen the fragment necessary for work, and highlight it in colors while working.

Another function is adjusting the schedule according to sanitary and hygienic standards.

Using a macro - a microprogram that allows you to perform operations automatically, in the schedule for students, classrooms are changed to points corresponding to a given subject, their sum is calculated for each day of the week for classes and all this is summarized in a table located below the schedule, allowing you to evaluate the schedule from a hygienic point vision (see schedule fragment 3).

The program allows you to create graphs using the “Chart Wizard” for greater clarity or for submitting the schedule to the SES (see Diagram 1).

The program allows you to automate the execution of a significant part of the work, frees you from rewriting, allows you to quickly analyze the schedule regarding compliance with sanitary and hygienic standards, present the results of the analysis in graphical form, scale the output document, adjust the schedule, and perform work at a high graphic level.

2. Working with a time sheet and a log of missed and substituted lessons.

The timesheet book consists of sheets with templates for timesheets for salaries, advances, additional timesheets, etc., depending on the specifics of the educational institution.

The second stage of the work is filling out the following columns in the templates: personnel number, full name, subject, weekly hours, checking notebooks, class management. This work is carried out at the beginning of the school year with subsequent adjustments throughout the year. The remaining columns of the report card are filled out immediately before submitting the report card every month.

Journal of missed and substituted lessons.

When preparing a timesheet for submission to the accounting department, I use auxiliary tables located in the same book or in separate file is a log of missed and substituted lessons, where substituted lessons are calculated automatically using pivot tables.

When filling out data on missed and substituted lessons in the summary tables, a summary is automatically produced for the missed lessons (teacher, number of missed lessons, in which class, etc.) and similarly for the substituted lessons. This makes it easier and faster to fill out the report card (see fragment 4 - report card; tables No. 1, 2 - accounting for missed and substituted lessons).

When filling out a timesheet, the program allows you to simultaneously work with both different sheets and different files.

3. Working with tariffs

Preparation of this document always takes a lot of time and mainly involves rewriting. The sheets on which you have to work are very large and are not convenient to work with or store. It takes a lot of time to count and reconcile watches; here this is done by a machine. The accountant calculates such tariffs by machine, entering formulas. Throughout the year, whenever your rate changes, you can use the copy function, eliminating the rewrite work and getting the job done faster.

4. Working with a summary report of student progress.

Convenient form size. You can visualize your performance results graphically. The program allows you to automate calculations.

Organization of work using information technology makes it possible to improve the work culture, the quality and design of the output information product, and intensify the work of the head teacher.

Acceleration of information processing becomes possible due to the creation of a database, storage, correction and prompt retrieval of information.

The culture of work design is significantly improved, and it becomes possible to present documentation in text and graphic versions, on the scale required for work.

The amount of repetitive work based on rewriting information and making minor changes is reduced through the automation of information updating processes, the creation and use of templates.

The article did not set out to describe all the possibilities of using computer technology in the work of a head teacher. The emphasis is on the practical solution of several urgent tasks performed in everyday work by the head teacher, who is responsible for drawing up the schedule, tariffs, time sheets, etc.

I use it in my work this technology since 1997, I am ready to help anyone who is interested.