Contains paid Promotion

Advertisement
Advertisement

Create a vacation planner 2019 with Excel

Create Excel vacation planner with holidays

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.

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.

Anzeige

Create a vacation planner 2019 with Excel

Create Excel vacation planner with holidays

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.

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.

Anzeige

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.

300x250
Anzeige

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
Lemonade Hausratversicherung
Anzeige

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.

300x250
Anzeige

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. 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

Arbeiten mit EDATUM
Excel Kalender Kopiersteuerung
Lemonade Hausratversicherung
Anzeige

2. Formatting the weekends

2. Formatting the weekends

Next we can now highlight the weekends visually. For this purpose, we mark all days in our calendar, and then select the item “Conditional formatting” the point “Formula for determining the cells to be formatted”, and there for the “Saturday” first enter the following function: = weekday (B4; 2) = 6

Then we go to “formatting” and pick out a background or font color with which we want to highlight the day visually. Of course you can use any conceivable type of formatting here. In our example, we simply chose a green gradient.

After confirming “OK” and “Apply” all Saturdays will be displayed in the calendar with the selected formatting. In order to emphasize the Sundays now, use the same function only that this is then not 6 but 7 (Sunday as the 7th day of the week).

See picture (click to enlarge)

Excel Kalender Wochenenden formatieren
Excel Kalender Sonntage formatieren

Next we can now highlight the weekends visually. For this purpose, we mark all days in our calendar, and then select the item “Conditional formatting” the point “Formula for determining the cells to be formatted”, and there for the “Saturday” first enter the following function: = weekday (B4; 2) = 6

Then we go to “formatting” and pick out a background or font color with which we want to highlight the day visually. Of course you can use any conceivable type of formatting here. In our example, we simply chose a green gradient.

After confirming “OK” and “Apply” all Saturdays will be displayed in the calendar with the selected formatting. In order to emphasize the Sundays now, use the same function only that this is then not 6 but 7 (Sunday as the 7th day of the week).

See picture (click to enlarge)

Excel Kalender Wochenenden formatieren
Excel Kalender Sonntage formatieren
Anzeige
Lemonade Hausrat
Anzeige

3. Enter holidays

3. Enter holidays

With the visual highlighting of the weekends we have already made a good deal. What is still missing are the holidays.

In order to be able to use the calendar dynamically for the following years, we recommend to create a list with the holidays, to which the calendar can later refer. If you miss a day later, or move it, you only need to change this relatively short list of holidays.

You can find a list of public holidays at:  www.kalender-365.eu or simply copy our list for 2019.

Public holidays 2019 (Germany)
01. Jan New Year 2019 Tuesday
06. Jan Holy Three Kings 2019 Sunday
19. Apr Karfreitag 2019 Friday
21. Apr Easter2019 Sunday
21. Apr Easter Sunday2019 Sunday
22. Apr Easter Monday2019 Monday
01. Mai Tag der Arbeit 2019 Wendsday
30. Mai Ascension 2019 Thursday
09. Jun Whit Sunday2019 Sunday
10. Jun Whit Monday2019 Monday
20. Jun Corpus Christi2019 Thursday
03. Okt Tag der Deutschen Einheit 2019 Thursday
31. Okt Reformation Day 2019 Thursday
01. Nov All Saints’ Day2019 Friday
25. Dez 1. Christmas Day 2019 Wendsday
26. Dez 2. Christmas Day 2019 Thursday
Anzeige

To highlight the holidays, we again go through the “conditional formatting” step and work with a VLOOKUP reference to the holiday list.

– Mark all days in the calendar
“Conditional formatting” & “Use formula to find cells to format”
– Insert the following function: = VLOOKUP (B4;$O$4:$ O$ 34;1;0)
– Format and select desired highlighting

Note:
With the used VLOOKUP we refer to the 01.01 with B4 and the values $$4:$O$34 have absolute reference to the list with the holidays. Of course, depending on where you use this list, this may be slightly different for you. You can read more about VLOOKUP and HLOOKUP here >>>

See picture (click to enlarge)

Feiertage in Excel Kalender hervorheben
300x250
Anzeige

With the visual highlighting of the weekends we have already made a good deal. What is still missing are the holidays.

In order to be able to use the calendar dynamically for the following years, we recommend to create a list with the holidays, to which the calendar can later refer. If you miss a day later, or move it, you only need to change this relatively short list of holidays.

You can find a list of public holidays at:  www.kalender-365.eu or simply copy our list for 2019.

Public holidays 2019 (Germany)
01. Jan New Year 2019 Tuesday
06. Jan Holy Three Kings 2019 Sunday
19. Apr Karfreitag 2019 Friday
21. Apr Easter2019 Sunday
21. Apr Easter Sunday2019 Sunday
22. Apr Easter Monday2019 Monday
01. Mai Tag der Arbeit 2019 Wendsday
30. Mai Ascension 2019 Thursday
09. Jun Whit Sunday2019 Sunday
10. Jun Whit Monday2019 Monday
20. Jun Corpus Christi2019 Thursday
03. Okt Tag der Deutschen Einheit 2019 Thursday
31. Okt Reformation Day 2019 Thursday
01. Nov All Saints’ Day2019 Friday
25. Dez 1. Christmas Day 2019 Wendsday
26. Dez 2. Christmas Day 2019 Thursday
Anzeige

To highlight the holidays, we again go through the “conditional formatting” step and work with a VLOOKUP reference to the holiday list.

– Mark all days in the calendar
“Conditional formatting” & “Use formula to find cells to format”
– Insert the following function: = VLOOKUP (B4;$O$4:$ O$ 34;1;0)
– Format and select desired highlighting

Note:
With the used VLOOKUP we refer to the 01.01 with B4 and the values $$4:$O$34 have absolute reference to the list with the holidays. Of course, depending on where you use this list, this may be slightly different for you. You can read more about VLOOKUP and HLOOKUP here >>>

See picture (click to enlarge)

Feiertage in Excel Kalender hervorheben
300x250
Anzeige

4. Visually adjust holiday calendar

4. Visually adjust holiday calendar

First, let’s format the appearance of the dates differently. Because the indication with year is actually superfluous. To do this we mark all date values ​​in the calendar and go over the cell formatting, select there “Custom” and enter TT. TTT. This will only display the weekday in abbreviated form and the date in two digits.

In order to create space for entries for each date, we mark the columns C – N and select “Insert cells” via the context menu. The newly created columns will all be given a background color formatting inherited from the previous column. But you can easily remove them using “Delete” & “Delete Formats”.

As an intermediate step, your calendar might look like this with an adjusted line height:

See picture (click to enlarge)

Datumswerte in Excel formatieren
Excel Urlaubsplanung Zwischenschritt

Of course, you can now make all sorts of adjustments according to your own wishes, and appropriate borders or fonts and much more. to change. The best thing about this form of calendar, however, is that you can reuse it for every year. Because you only need to change the year number above as the main reference point so that the Excel holiday calendar dynamically adjusts all other values.

If the whole thing was a bit too complex, you can download the calendar (which we upgraded visually) for free.

You can easily adapt this to your own needs. Tip: You could also use the “Conditional Formatting” for entries such as HOLIDAY or SICK and, for example, enter them in color.

Note:
If you change the year on our calendar, you may be surprised that the holidays are suddenly not displayed for the selected year. This is because the date values ​​in the holiday table refer to the VLOOKUP not only the day but also the year, and of course these are no longer found in the table in accordance with the changed year.

However, this can be easily solved by also entering the holiday values ​​in the table with the appropriate year after changing the year.

Download holiday planner 2019

First, let’s format the appearance of the dates differently. Because the indication with year is actually superfluous. To do this we mark all date values ​​in the calendar and go over the cell formatting, select there “Custom” and enter TT. TTT. This will only display the weekday in abbreviated form and the date in two digits.

In order to create space for entries for each date, we mark the columns C – N and select “Insert cells” via the context menu. The newly created columns will all be given a background color formatting inherited from the previous column. But you can easily remove them using “Delete” & “Delete Formats”.

As an intermediate step, your calendar might look like this with an adjusted line height:

See picture (click to enlarge)

Datumswerte in Excel formatieren
Excel Urlaubsplanung Zwischenschritt

Of course, you can now make all sorts of adjustments according to your own wishes, and appropriate borders or fonts and much more. to change. The best thing about this form of calendar, however, is that you can reuse it for every year. Because you only need to change the year number above as the main reference point so that the Excel holiday calendar dynamically adjusts all other values.

If the whole thing was a bit too complex, you can download the calendar (which we upgraded visually) for free.

You can easily adapt this to your own needs. Tip: You could also use the “Conditional Formatting” for entries such as HOLIDAY or SICK and, for example, enter them in color.

Note:
If you change the year on our calendar, you may be surprised that the holidays are suddenly not displayed for the selected year. This is because the date values ​​in the holiday table refer to the VLOOKUP not only the day but also the year, and of course these are no longer found in the table in accordance with the changed year.

However, this can be easily solved by also entering the holiday values ​​in the table with the appropriate year after changing the year.

Download holiday planner 2019
Anzeige
Anzeige

Popular Posts:

Anzeige

Popular Posts:

Monthly Bestseller Charts:

Monthly Bestseller Charts:

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige