For those who just want to download the calendar in Excel for the current year, the picture below shows what it looks like. Holidays are marked in red, weekends are marked in pink. The current day is also automatically highlighted with a fill.

Step-by-step instructions for creating a calendar in Excel

Now we will create an automatic calendar that will be useful not only for the current year, but also for all subsequent years. And for this we do not need macros and programming. All that I will show is done using standard Excel features.

Making headlines

In cell A1, enter the following formula: ="Calendar for " & YEAR(TODAY()) & " year". Function TODAY() returns the current date (we will use it often). Accordingly, a bunch of functions YEAR(TODAY()) will return the current year to us.

Now let's create the month headings. In the cell where January stands, write the following formula =DATE(YEAR(TODAY()),1,1). This function returns the first of January of the current year (01/01/2015 in my case). Let's apply a format to this cell so that only the name of the month is displayed. To do this, open the cell format with a keyboard shortcut ctrl+1 and select the appropriate format (see figure).

To create the titles of the next months, we will use another very useful function. =DATE(B3,1). This function returns a date that is a given number of months away from the specified one (in our case, one). Thus, we get the following picture (in addition, I added a little formatting):

We put down the numbers

To automatically put down numbers in the calendar, we will use only one formula - the array formula. It looks scary, but if you figure it out, everything is easy and simple. Select cells B5:H10 and enter the following formula (do not rush to press Enter):
=DATE(YEAR(B3),MONTH(B3),1-1)-(WEEKDAY(DATE(YEAR(B3),MONTH(B3),1-1))-1)+(0:1:2:3 :4:5:6)*7+(1;2;3;4;5;6;7)

Instead of Enter, press the keyboard shortcut Ctrl+Shift+Enter, thus inserting an array formula. As a result, in the selected range, we get the numbers of the month specified in cell B3.

We carry out a similar operation with other months or simply copy the date range of January into the following months. We get the following picture:

Formatting

Let's format the cells so that they contain only numbers (not full dates). Select the dates, open the cell format ( ctrl+1) and manually enter the following format:

The calendar is getting more familiar.

Adding conditional formatting

Now in January, the calendar contains the dates December 29, 30, 31, as well as part of the February dates. Let's hide these numbers a bit. To do this, we will use conditional formatting. Select cells B5:H10 and create a conditional formatting rule. Cells will be formatted for which the following formula will be TRUE:
=AND(MONTH(B5)<>1+3*(PRIVATE(ROW(B5)-5,9))+PRIVATE(COLUMN(B5),9))

As the format, I chose the gray text of the font and got the following picture:

Automatically highlight the current date in the calendar

To do this, we create another conditional formatting rule. The cell will only be formatted if its value is equal to the current date ( =TODAY()):

Highlight weekends in Excel calendar

This time we will manage with the usual filling of cells. Choose to your taste. I got it like this:

Adding holidays

Let's create a new Excel sheet ( Holidays) and add a smart table to it with a column Holidays. This column contains all holidays. I have already done this, you can safely copy and use.

Let's go back to our calendar and create another conditional formatting rule. The conditional formatting formula will check if the date is a holiday. And if the formula returns TRUE, the cell will be formatted. Here is the formula:
=NOT(ISERROR(MATCH(B5,INDIRECT("Holidays[Holidays]");0))). It is worth noting that when adding or changing holidays, the calendar will be automatically formatted.

It remains to copy cells B5:H10 to other months and our automatic calendar is ready and can serve us for more than one year.

Change the color scheme of an Excel calendar

If you use theme colors when creating a calendar, then you can change them later with one simple movement. By simply changing the theme colors on the tab Page layout. Choose the one you like.

P.S.

The production calendar (or calendar of working and holidays) is one of the important components of many reports and planning tools.

Due to floating weekends and holidays in Russia, we cannot use the same data set every year.

Personally, every year I have a question - where to download the production calendar. Preferably in a convenient Excel format and with the least effort. And just recently, I found a solution for myself, which, I am sure, will be useful to many.

To automate the process, we need

• Microsoft Excel (2010, 2013 or 2016)
• Page on the Russian Open Data portal where you can download the production calendar in csv format: http://data.gov.ru/opendata/7708660670-proizvcalendar.

* for Excel 2010 and 2013 you will need the free Power Query add-in, you can download it from here.

How the page looks at the time of writing this article - the ability to download the current version of the production calendar

Previous versions and the xlsx version of the production calendar are also available on the page:

In Excel 2016, to solve similar problems, we have a group of buttons “Download and Convert”(Get & Transform) on the menu tab "Data"(Data).

For Excel 2010 and Excel 2013, the same functionality is available as a free Power Query add-in, which can be downloaded from here. The functionality of the add-on is evolving every month, I recommend updating regularly to always have the latest features and data transformation capabilities at your fingertips.

When all the necessary programs are installed and the source of information is examined, let's return to the task.

To download something from the Internet, we need a link to a file or page.

The Open Data Portal publishes data sets and necessarily accompanies each set with a Passport.

The passport contains important information about the set, from which, in particular, we can take the “Hyperlink (URL) to the data set”

Let's copy the URL and make a request "From the Internet".

Now we need to transform the received data.

• Using the first row as column headings

Let's select the columns we need. Let's use the function “Select columns”(Select Columns)

For the convenience of filtering, change the format of the column “Year/Month” to “Integer”

Let's use the filter, leave only the years of interest to us

Let's set the filter with the conditions we need

Rename the columns to serial numbers of months and “Year/Month” to “Year”

This action turns the table into something terrible with a bunch of columns. In addition, there is an uninvited step - “Changed type1”, which must be removed.

To bring the table back to normal, we use the function again “Uncollapse other columns”(Unpivot Other Columns)

The result is a table

• Delete the column “Attribute.1” formed from the column headings

• Finally, you can add a column with a date

We use the formula

• Having received a column with a date, you can remove the previous columns

With a similar request, we receive an incomplete list of calendar dates, since the set published on the Open Data portal contains only holidays and pre-holiday days.

The production calendar is used by accountants and personnel officers to calculate the norm of working hours for months, quarters, for the whole year 2016, and for -40, -36, -24 hourly working weeks. The production calendar defines working and holidays and pre-holiday days, allows you to calculate the wages of employees, vacation, maternity and other benefits. The calendar was approved by Resolution No. 1017 of September 24, 2015.

Working hours

The table below shows the norms of working hours for months, quarters and for the year as a whole.

Holiday weekend 2016

In accordance with the Labor Code of the Russian Federation (Article 112 part) of the Federal Law of April 23, 2012 No. 35-FZ, the following days are recognized as completely non-working holidays in the Russian Federation:

Completely non-working holidays of the production calendar

Below are the non-working days for the current year.

• January 1, 2, 3, 4, 5, 6 and 8 - New Year holidays;
• January 7 - Christmas;
• February 23 - Defender of the Fatherland Day;
• March 8 - International Women's Day;
• May 1 - Spring and Labor Day;
• May 9 - Victory Day;
• June 12 - Day of Russia;
• November 4 - National Unity Day.

Shortened working days in 2016

Shortened working days in the production calendar:

February 20th,
the 3rd of November,
Working days or shifts, regardless of the nature of the work, are one hour shorter.

Transfer of holidays in the production calendar

In 2016, due to the coincidence of holidays with weekends, the days off are transferred in the production calendar of the following days:

• January 2 (Saturday) → May 3 (Tuesday)
• January 3 (Sunday) → March 7 (Monday)
• February 20 (Saturday) → February 22 (Monday)

According to the second part of Art. 112 of the Labor Code of the Russian Federation, if a day off and a non-working holiday coincide, the day off is transferred to the next working day after the holiday, with the exception of days off coinciding with non-working holidays from January 1 to January 8.

In 2016, due to the coincidence of non-working holidays with days off, a transfer is made in the production calendar of days off: from January 2 (Saturday) to May 3 (Tuesday); from January 3 (Sunday) to March 7 (Monday); from February 20 (Saturday) to February 22 (Monday).

Video instruction: “Government approved production calendar for 2016”

Video instruction from Consultant Plus on the norms of working hours for 2016 for -40, -36 and -20 hour working weeks.

We're sorry, the browser you are using is limiting the functionality of this website. To get the best experience, we recommend that you switch to an up-to-date fully supported web browser. If you feel that you have received this message in error, please contact us.



Modern life is full of events. Keeping track of everything from regular meetings, projects, and urgent assignments to what's going on in your personal life can be tricky.

Calendars are essential for organizing all your tasks, meetings, appointments, and events. The possibilities of calendars are wide: they can be used to track work time, create a work or class schedule, track a project, and much more.

You can create a calendar for a month or a year in several different ways. It can be created manually from scratch using Excel, however this can take a lot of time and effort. You can also use a pre-made calendar template in Excel with formatting already configured. There are many calendar templates for Excel: yearly calendars, monthly calendars, weekday views, and calendars with notes. These are ready-made, already formatted calendars to which you can immediately add events.

How to choose the most convenient template from such a variety? We explored all the options and found the best Excel template for creating a calendar for both the month and the year.

smartsheet

In this article

Download Monthly or Yearly Calendar Template for Excel

Download 2019 Monthly Calendar Template (Landscape)

Download 2020 Monthly Calendar Template (Landscape)

Download 2019 Monthly Calendar Template (Portrait)

Download 2020 Monthly Calendar Template (Portrait)

Using a Monthly or Yearly Calendar Template in Excel

The calendar template is extremely easy to use. You just need to select a calendar: for a month or for a year - and add scheduled events to it. You can also customize fonts, their sizes and colors. If you have selected a monthly calendar, you first need to change the name and dates for the specific month.

Below are additional instructions for customizing the template.

1. Format the calendar template for a month

1. To change the name, double-click its field, delete the formula, and enter the name of the new month.
2. Then you need to renumber the date fields. You can enter the dates manually or use the AutoComplete feature (see step 4 in "Creating a Monthly Calendar in Excel 2003, 2007, and 2010").

2. Add events to the calendar template for a month or a year

1. In any of the templates, double-click the cell in the date field and enter the event. To enter another event on the same date, click another cell in the same date field.
2. To center text, click the cell. Then on the tab home in Group alignment click the Center icon (which looks like five lines of text aligned in the center).

Change fonts and colors

1. Click the cell with the text you want to change, then on the tab home change the font, size, or color, or make the text bold, italic, or underlined.
2. To change the background color for the name of a day of the week or an event, select the cell, click the paint can icon, and choose a fill color.

You can also personalize the calendar template by adding an image, such as your company logo. On the tab Insert press the button Drawings. Upload the desired image. It will appear in the table, after which you can drag it to the desired location.

Creating a Monthly Calendar in Excel 2003, 2007 and 2010

Following are the step by step instructions to create monthly or yearly calendar in Excel.

1. Adding the names of the days of the week

First you need to add the names of the days of the week as headings, as well as the name of the month.

1. Leave the first row in the table blank. In the second line, enter the names of the days of the week (one per cell).
2. To format the headings and adjust the spacing between them, highlight the entered names of the days of the week, and then on the tab home in Group cells press the button Format. Select an item Column Width and set the width to around 15-20 depending on how wide you want the calendar to be.

2. Adding a calendar title

1. In the first empty line, we will add the current month as the name of the calendar using a formula. Click any cell in the first row and in the field fx above it, type "=TODAY()". This field will now display the current date.

1. The date format is not correct yet. To fix it, click the cell with the date. On the tab home in Group Number click the arrow next to the field date. Select an item Other number formats and select the desired format for the month name.
2. To center the title, select all the cells in the title row (including the one that displays the name of the month) and click on the tab home button Merge and center.

3. Create days in the calendar

Now let's create the main area of ​​the calendar. We use borders to create date fields.

1. First, select the entire table.
2. On the tab home click the paint can icon and choose white as the color. The background of the table should change to white.

1. Then select five or six cells under the name of the first day of the week (Monday).

1. Click the border icon on the tab home and select the Outer Borders option. A frame appears around the first date field in the row.
2. Select the created field, copy it and paste it under the names of the rest of the days of the week. Now for all days of the week there will be the same fields.
3. Repeat these steps for four more rows in the table. The calendar should now look like this:

To add borders around the names of the days of the week, select the line with them, click the borders icon and select the All Borders option.

4. Adding dates

We have created a grid for the calendar. Now it's time to add the dates. You can enter the date in each field manually, or you can use Excel's auto-complete feature. Here's how to do it:

1. On each calendar row, enter the first two dates of the week in the first cells of each field. For example, if the first of the month is Wednesday, enter 1 for Wednesday and 2 for Thursday.
2. Then, holding down the key SHIFT, select both cells with numbers.
3. Drag the lower right corner of the selected cells to automatically fill in the rest of the row.
4. Repeat these steps for the entire month.

Note. You must manually enter the first two dates on each row before filling the entire week with drag and drop.

Create a calendar for a year in Excel

So, you have created a calendar template for a month. If you want to use this template for individual months, you can simply change the name of the month and renumber the days.

However, you can also create a yearly calendar using this template.

1. At the bottom of the table window, right-click the Sheet1 tab.
2. Choose a team Move or copy.
3. Check box Create a copy and press the button OK.

1. Create a total of 12 copies, one for each month. Note. For months with 31 days, you need to add one more row to the calendar.

After creating 12 copies, you need to change the name of the month in each of them. Also, you need to renumber the dates accordingly. You can do this manually or use the autocomplete feature (see step 4 in "Creating a Monthly Calendar in Excel 2003, 2007, and 2010").

Setting up a calendar in Excel

In Excel, you can easily set up a calendar for a month or a year. You can color-code certain events, such as appointments or birthdays, or change the font size. Moreover, the company logo can be added to the calendar.

1. Font Formatting

1. To enlarge the name, click on the line with it. On the tab home You can change the font and font size, and make the title bold, italic, or underlined.
2. To change the font size for the names of the days of the week, select them all. On the tab home You can change the font type and size.

1. To format dates, select all date fields. On the tab home You can customize the font type and size.

2. Changing colors

You can change the font and background colors of the calendar. Color coding can be especially useful for highlighting certain types of events.

1. To change the color of the name, click on the line with it. On the tab home select the desired color from the drop-down list.
2. To change the background color for weekday names, select the entire row, click the paint can icon, and choose a fill color. You can also change the text color as described in step 1.
3. To highlight an event, enter it in the date field. Then select the text, click the paint can icon, and choose a fill color.

3. Adding an image

You can personalize the calendar by adding images such as your company logo.

1. On the tab Insert press the button Drawings. Upload the desired image.
2. It will appear in the table, after which you can drag it to the desired location.

If you want to put a logo or picture at the top of the calendar, you need to add space for it.

1. Right-click the first line with the name and select command Insert.
2. Select an item string.
3. Repeat these steps to insert as many extra rows as needed.
4. To make the background of new lines white, select them, click the paint can icon, and choose white.
5. To remove the grid line above the title bar, select the title bar, click the grid icon, and select the no grid lines option.

Print a calendar in Excel

Printing a customized and formatted calendar can be difficult. The calendar may not fit on one printed page, so part of it will be printed on the second page. To fix this, follow the steps below.

1. On the tab Page layout select Orientation > landscape.

1. In Group Fit change the width and height to "1 page".

The calendar will now print on one page.

Finding Microsoft Calendar Templates

Microsoft also offers quite a few calendar templates. You can choose from multi-page calendar, yearly calendar, weekly calendar and more.

To open a finished template in Excel, follow these steps:

1. Click File > Create.
2. Enter "Calendar" in the search field.
3. Many options will appear. For this example, choose a template Monthly calendar for any year and press the button Create.

A table with headings will open. calendar month, calendar year and First day of the week.

1. Select the January cell and click the arrow that appears. Select the calendar month from the drop-down menu.
2. Enter the calendar year in the cell below the month.
3. Select the "Monday" cell and click the arrow that appears. Select the first day of the month from the drop-down menu.

You can also view the collection of Microsoft templates online. To do this, select the "Calendars" category on the left.

Add a calendar using Visual Basic

You can insert a pre-populated calendar directly into Excel using the CalendarMaker macro in the Visual Basic Editor. To do this, you need to enable developer mode in Excel and use a programming language, but this is quite simple, especially since Microsoft offers sample code.

1. Enable developer mode

First you need to enable developer mode.

1. Click File > Parameters.
2. In the pop-up window, select a category on the left Customize Ribbon.
3. In the area of Main tabs check the box Developer.

A new tab appears on the ribbon at the top of the Excel window.

2. Add a calendar using Visual Basic for Applications code

Smartsheet pre-made templates let you create a calendar in no time. Months, days of the week, and dates are pre-formatted, plus you have room to add descriptions, comments, and the duration of each event in hours.

Let's see how to use a calendar template in Smartsheet.

1. Choose a calendar template

1. Go to Smartsheet.com and sign in to your account (or start your free 30-day trial).
2. On the tab Home press the button Create and select the item View templates.
3. In field search type the word "calendar" and click the magnifying glass icon.
4. Several results will appear. For this example, choose a template Daily calendar for 2016 and press the blue button Use in the upper right corner.
5. Give the template a name, select a folder to save it, and click the button OK.

2. Adding information to the calendar

A pre-made template will open with months and dates formatted for the entire year 2016. For example, there is also filled content.

1. Add events in a column Actions. You can also add additional data in columns Description, Clock and

Production calendar 2016, approved by the government of the Russian Federation,- an irreplaceable thing for accountants and personnel officers in order to determine the number of working days in the reporting period and for other calculations. You will learn about its features in 2016 from our material.

Approved for printing time sheet calendar for 2016

This table with dates has the usual look for us with a red mark for weekends and holidays. Main difference production calendar 2016, approved by the government of the Russian Federation, from its simple counterpart - available information:

• on the number of working days, weekends and holidays;
• the norm of time that an employee must work, taking into account the hourly duration of the working week (40, 36 and 24 hours).

A new calendar is drawn up for each new year. The required information is provided monthly. Then it is summarized in the results for each quarter, half year and year.

Production calendar with weeks for a five-day working week for 2016

Five days is one of the most common types of work week. It has found wide application in various enterprises and institutions. Especially the five-day work schedule is typical for the office sector.

In the five-day mode work:

• organizations and institutions of the public sector (state, autonomous, budgetary institutions, public authorities);
• accounting;
• credit institutions, among which there may be exceptions: often large banks prefer to have several working offices on Saturday;
• health care facilities, but there are exceptions among them;
• kindergartens;
• and many others.

The five-day week implies a 5-day working regime from Monday to Friday and 2 days of rest (Saturday and Sunday).

The production calendar is necessary to account for the time actually worked by employees in the company. Recall that this is the direct responsibility of the organization.

Find out the features of keeping records of working hours from our article .

Working time, expressed in hours, in 2016 will be:

• 1974 hours for a week of 40 hours;
• 1776.4 hours for a weekly load of 36 hours.
• and 1183.6 hours for the shortest 24-hour week.

Production calendar for 2016 with holidays and days off

In Art. 112 of the Labor Code of the Russian Federation, you can see a list of officially established non-working holidays.

There are 14 of them in total. Often you can hear another name for the holidays - “red days of the calendar”. Let's consider in more detail production calendar 2016 with holidays:

• January.

The numbers from 1st to 6th, as well as January 8th, are days of holidays on the occasion of the New Year. January 7 is a public holiday.

• February.

This month falls on the men's holiday - February 23. Saturday the 20th is a working day for rest on Monday the 22nd. At the same time, the duration of the working Saturday is also reduced by 1 hour.

• March.

And in March there is a world women's holiday - the 8th day. Monday the 7th is also a day of rest, as January 3rd is moved to it.

There are 2 holidays in May - the 1st and 9th. At the same time, May 1 falls on Sunday and for this reason is postponed to May 2. And on the 3rd, employees will have a rest for January 2.

• June.

In the first month of summer, there is 1 public holiday (12th), which falls on Sunday and is moved to Monday, the 13th.

• November.

The 4th is a public holiday, so Thursday the 3rd should be shortened by 1 hour.

• December.

December 31st Saturday is not a public holiday, so there are only 9 regular days off this month.

Production calendar 2016, approved by the government of the Russian Federation, with comments

The production calendar for 2016 is characterized by the following data:

• 366 calendar days;
• 247 working weekdays;
• and 119 non-working days, which is one third of the total and half of the working days.

The decision to transfer holidays to working days in this or next months is made by the Government of the Russian Federation in the form of resolutions. In the current year, the Decree of the Government of the Russian Federation dated September 24, 2015 No. 1017 is relevant.

Download for free in Excel and print the production calendar for 2016

The production calendar should always be at hand for an accountant who deals with payroll. Knowing working days, weekends and holidays will help to avoid errors in payroll, for example, it will tell you about double pay for going to work on weekends and holidays, calculating vacation if it falls on non-working holidays, and so on.

Opportunity download production calendar 2016 will be very convenient to place in front of you in a visual printed format.

The production calendar for 2016 can be downloaded for free on our website.

Results

The production calendar occupies an important place in the work of every accountant who deals with the calculation of wages for employees. It helps to avoid errors in the calculation of wages and other payments. The production calendar includes monthly and summary (quarterly, half-yearly and yearly) data on working days, weekends and holidays, as well as working hours in hours. The production calendar is also necessary to record the working time worked by the organization's personnel and to schedule its accounting. Production calendar for 2016 download You can print it on our website.