Contains paid Promotion

Advertisement

Create a vacation planner 2020 with Excel

At the latest in the first week of the new year already begins the vacation planning for the upcoming season.
Excel makes it easy to create a dynamic holiday calendar that you can reuse every year.

Create Excel vacation planner with holidays

Topic Overview

Anzeige

In our article, we would like to explain how you can put together such a holiday planner with highlighting of holidays and weekends in Excel, without it being too confusing.

So lohnen sich die Amazon Visakarten

Create a vacation planner 2020 with Excel

At the latest in the first week of the new year already begins the vacation planning for the upcoming season.
Excel makes it easy to create a dynamic holiday calendar that you can reuse every year.

Create Excel vacation planner with holidays

Topic Overview

Anzeige

In our article, we would like to explain how you can put together such a holiday planner with highlighting of holidays and weekends in Excel, without it being too confusing.

So lohnen sich die Amazon Visakarten

1. Structure of the calendar

1. Structure of the calendar

We’ll start by simply entering the first month of January in a cell and then touching that cell on the lower right with the left mouse button and copying it to the right until December.

Above the line with the months, we insert at any point the year, which will later serve as an important reference point for the further creation of the vacation planner. Optionally, you can also insert a heading such as HOLIDAY PLANNER. However, not in the same cell as the year.

Note:
Incidentally, it is generally advisable to work at least one column to the left for such work, and to release one line upwards. Because, first of all, it does not look so bruised later, and secondly, even if we work with fixed column or line references, we still have enough air to adjust columns and rows in size.

Advertisement

Next we add the following function to the cell just below the “January”: = DATE (H2;1;1)

The underlying cell now has a slightly more complex function that will make our date function copyable to the extent that we do not have overlaps in the months following each month. In addition to this cell comes the following function: = FAILURE (IF (MONTH (B4+1) = MONTH (B$4); B4+1;””); “”))

This function can now be copied down to the 31.01 at the lower right corner of the cell.

See picture (click to enlarge)

Kalenderstruktur aufbauen
Kalenderfunktion mit WENNFEHLER

Now we put in the cell under the month “February” the following function: = EDATUM (B4;1)
With this, we refer to the last day of the month “January” and add 1 day. What then corresponds to the 01.02.

We then copy this function again until December, so that the 1st of each month is displayed.

With this preliminary work, we have created a basis with which we can complete the rest of the calendar on the copy control completely Excel. For this we mark the cell with the 2:01 to 31:01 down, and then to the right until December. As a result, we have now presented the full year, and can use this calendar as the basis for our next steps.

See picture (click to enlarge)

Arbeiten mit EDATUM
Excel Kalender Kopiersteuerung
Advertisement

We’ll start by simply entering the first month of January in a cell and then touching that cell on the lower right with the left mouse button and copying it to the right until December.

Above the line with the months, we insert at any point the year, which will later serve as an important reference point for the further creation of the vacation planner. Optionally, you can also insert a heading such as HOLIDAY PLANNER. However, not in the same cell as the year.

Note:
Incidentally, it is generally advisable to work at least one column to the left for such work, and to release one line upwards. Because, first of all, it does not look so bruised later, and secondly, even if we work with fixed column or line references, we still have enough air to adjust columns and rows in size.

Advertisement

Next we add the following function to the cell just below the “January”: = DATE (H2;1;1)

The underlying cell now has a slightly more complex function that will make our date function copyable to the extent that we do not have overlaps in the months following each month. In addition to this cell comes the following function: = FAILURE (IF (MONTH (B4+1) = MONTH (B$4); B4+1;””); “”))

This function can now be copied down to the 31.01 at the lower right corner of the cell.

See picture

Kalenderstruktur aufbauen
Kalenderfunktion mit WENNFEHLER

Now we put in the cell under the month “February” the following function: = EDATUM (B4;1)
With this, we refer to the last day of the month “January” and add 1 day. W