Vacation schedule template (or training schedule or other schedule) in MS Excel file

I work as a small manager and am responsible for drawing up and keeping up to date the schedule of vacations for my department. This schedule is drawn up at the end of the year for the next year and is provided to the personnel department of the organization. At the same time, the personnel department needs to provide it in the format of a table-list, but for myself I need a format of visual graphics for work. In addition, due to the constant postponement of employee leave, this schedule needs to be kept up to date.



Do not do unnecessary work and everything that can be automated for me is a life principle. In this article I want to share the experience of creating a MS EXCEL graphic file. Perhaps the resulting pattern, or this experience will be useful to you.



For those who need this template and who do not want to bother too much about how it works - immediately Link to view and download .



For those interested in construction, the following description.

')

The origin of the task

So. The required personnel format is shown in the picture below (all names and positions are fictional):





Features of this format:



1. In the table, separate lines include separate periods of holidays.

2. The table shows the dates of commencement of leave and the duration

3. The list is arranged alphabetically by the names of the employees and ascending the start dates

The schedule is a schedule

The vacation schedule of my employees is also needed by me, but I would like to have it in the form of a vivid schedule (chart), where employee vacation periods are reflected along the time axis. And I finally did it - like this:

How is it done

To create a graph of this form, I used the Chart Designer built in MS EXCEL and the “Line with accumulation” chart type.





In order for the horizontal axis of the chart to look like a time scale, the following settings are needed:





The maximum and minimum correspond to the numerical values ​​of the dates of the beginning and end of the year. In order for the seven-day grid to coincide with the real weeks for the start date of the year, it is better not to take 01.01, but the closest Monday to this date.



The table above the graph is used as the initial data of the formation of the graph. The print area of ​​the page is configured so that it does not appear.





In fact, the diagram reflects not only the periods of vacations, but also the gaps between them (the settings show the holidays in green fill color, and the discontinuities are shown without transparent or transparent fill).

First row

This is a transparent period from the beginning of time to the date of the beginning of the first holiday of the year. The value in the “Start1” column is used.

Second row

This is the first vacation shown in green. The value used in the “Days 1” column is the duration of the first vacation period:





My “Days 1” column is calculated using the formula:





Plus one day because the date of the end of vacation is his last day, not the first working day.

Third row

This is a transparent period from the end of the first holiday to the beginning of the second.





It is also considered a formula, and since this value has no user value - the column in the table is as narrow as possible.

Subsequent rows

Here you just want to say "well, and so on ...", in general, green periods of holidays are constructed similarly to row 2, and transparent gaps between them are similar to row 3. For my task 5 periods were enough - this is the current limitation of the template, which can overcome by continuing the table wide (as far as you have enough patience).

And what about personnel officers?

They just need a list !?



Not to keep the same data in 2 places, not to create the possibility of their discrepancy is a matter of honor for me. They may have to spend time, but it is better to enter the formulas once rather than edit the data each time. There are no difficulties here - just links from a sheet containing the form for personnel officers to cells, all in the same source table.





Such links are filled in each row of the cell from B to E. For each row from the source table (each employee) are created, respectively, the number of possible vacation periods - 5 rows in this table. For example, the field E "Number of calendars. days ", for the first employee filled:

1st line - "= Graph! G5"

2nd line - "= Graph! K5"

3rd line - "= Graph! O5"

4th line - "= Graph! S5"

5th line - "= Graph! W5"

For the next employee, the links will be on the same columns and on the next line (it was quite laborious to fill out because the form was transposed, and I didn’t figure out how to copy formulas with transposition).



Notice that there is a filter in column E. It is needed in order to display only the completed periods of holidays (set not to display 0).



It remains to automate the numbering of rows (first column). The first line contains the number “1” in hands, for the rest I use the formula "= A6 + IF (E7 = 0; 0; 1)" (using the 2nd line as an example).



That's all. Thank you for attention