Anzeige

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

The question of many users how to create a calendar in Excel, and then preferably also with the display of the weekends, calendar week and public holidays, keep recurring at regular intervals. And rightly so, because it’s not that easy, and some people give up in frustration.

In Microsoft Excel, with a little practice, you can easily build an annual calendar / vacation planner with numerous functions according to your ideas.

Microsoft Excel: Urlaubsplaner 2022 mit Feiertagen und Wochenenden erstellen

Topic Overview

Anzeige

In our example we would like to explain step by step how you can put together an Excel vacation planner 2022 with highlighting of holidays and weekends in Excel, without it becoming too confusing. In the beginning it is a bit of work, but the further you get, the more ideas you have as a result of how you want to modify and expand your own Excel vacation planner.

Nord VPN testen
30 day money-back-guarantee
Advertisement

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

The question of many users how to create a calendar in Excel, and then preferably also with the display of the weekends, calendar week and public holidays, keep recurring at regular intervals. And rightly so, because it’s not that easy, and some people give up in frustration.

In Microsoft Excel, with a little practice, you can easily build an annual calendar / vacation planner with numerous functions according to your ideas.

Microsoft Excel: Urlaubsplaner 2022 mit Feiertagen und Wochenenden erstellen

Topic Overview

Anzeige

In our example we would like to explain step by step how you can put together an Excel vacation planner 2022 with highlighting of holidays and weekends in Excel, without it becoming too confusing. In the beginning it is a bit of work, but the further you get, the more ideas you have as a result of how you want to modify and expand your own Excel vacation planner.

Nord VPN testen
30 day money-back-guarantee
Advertisement

1. Structure of the calendar

1. Structure of the calendar

We start first by simply entering the first month “January” in a cell, and then touching this cell at the bottom right with the left mouse button and copying it over to “December” to the right.

Above the line with the months, we add the year at any point, 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. Not in the same cell as the year, however.

Note:
In principle, it is advisable to leave at least one column to the left and one line to the top for such work. Firstly, it doesn’t look so squashed later, and secondly, even if we work with fixed column or row references, we still have enough room to adjust the size of columns and rows.

Advertisement

Next we insert the following function in the cell directly below the “January”: = DATE (H2; 1; 1)

In the cell below there is now a somewhat more complex function that will serve to make our date function copyable so that we do not have any overlaps in the following months in the individual months. In addition, this cell contains the following function: = IFERROR (IF (MONTH (B4 + 1) = MONTH (B $ 4); B4 + 1; “”); “”)

We can now copy this function down to the 31.01 at the lower right corner of the cell.

See fig. (Click to enlarge)

Kalenderstruktur aufbauen
Kalenderfunktion mit WENNFEHLER

Now we insert the following function in the cell under the month “February”: = EDATE (B4; 1)
With this we refer to the last day of the month “January” and add 1 day to it. Which then corresponds to 02/01.

We then copy this function back over to December, so that the 1st of each month is displayed.

With this preliminary work we have created a basis with which we can have the rest of the calendar filled out completely by Excel using the copy control. To do this, we mark the cell with the 02.01 to the 31.01 downwards, and then to the right until December. As a result, we have now displayed the entire year and can use this calendar as the basis for our further steps.

See fig. (Click to enlarge)

Arbeiten mit EDATUM
Excel Kalender Kopiersteuerung
Advertisement

We start first by simply entering the first month “January” in a cell, and then touching this cell at the bottom right with the left mouse button and copying it over to “December” to the right.

Above the line with the months, we add the year at any point, 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. Not in the same cell as the year, however.

Note:
In principle, it is advisable to leave at least one column to the left and one line to the top for such work. Firstly, it doesn’t look so squashed later, and secondly, even if we work with fixed column or row references, we still have enough room to adjust the size of columns and rows.

Advertisement

Next we insert the following function in the cell directly below the “January”: = DATE (H2; 1; 1)

In the cell below there is now a somewhat more complex function that will serve to make our date function copyable so that we do not have any overlaps in the following months in the individual months. In addition, this cell contains the following function: = IFERROR (IF (MONTH (B4 + 1) = MONTH (B $ 4); B4 + 1; “”); “”)

We can now copy this function down to the 31.01 at the lower right corner of the cell.

See fig. (Click to enlarge)

Kalenderstruktur aufbauen
Kalenderfunktion mit WENNFEHLER

Now we insert the following function in the cell under the month “February”: = EDATE (B4; 1)
With this we refer to the last day of the month “January” and add 1 day to it. Which then corresponds to 02/01.

We then copy this function back over to December, so that the 1st of each month is displayed.

With this preliminary work we have created a basis with which we can have the rest of the calendar filled out completely by Excel using the copy control. To do this, we mark the cell with the 02.01 to the 31.01 downwards, and then to the right until December. As a result, we have now displayed the entire year and can use this calendar as the basis for our further steps.

See fig. (Click to enlarge)

Arbeiten mit EDATUM
Excel Kalender Kopiersteuerung
Advertisement

2. Formatting the weekends

2. Formatting the weekends

Next, we can now optically highlight the weekends. To do this, we mark all the days in our calendar, and then select the item “Use formula to determine the cells to be formatted” via the “Conditional formatting”, and enter the following function for the “Saturday” first: = weekday (B4; 2) = 6

Then we go to “Format” and look for a background or font color with which we want to highlight the day optically. Of course, you can use any form of formatting you can think of here. In our example we simply chose a green gradient.

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

See fig. (Click to enlarge)

Excel Kalender Wochenenden formatieren
Excel Kalender Sonntage formatieren

Next, we can now optically highlight the weekends. To do this, we mark all the days in our calendar, and then select the item “Use formula to determine the cells to be formatted” via the “Conditional formatting”, and enter the following function for the “Saturday” first: = weekday (B4; 2) = 6

Then we go to “Format” and look for a background or font color with which we want to highlight the day optically. Of course, you can use any form of formatting you can think of here. In our example we simply chose a green gradient.

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

See fig. (Click to enlarge)

Excel Kalender Wochenenden formatieren
Excel Kalender Sonntage formatieren

3. Enter the holidays

3. Enter the holidays

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

In order to be able to use the calendar later dynamically for the following years, we recommend creating a list of public holidays that the calendar can refer to later. If a day is omitted later or postponed, you only need to change this relatively short list of public holidays.

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

Holidays 2022 (Germany)
01. Jan Neujahr 2022 Samstag
06. Jan Heilige Drei Könige 2022 Donnerstag
15. Apr Karfreitag 2022 Freitag
17. Apr Ostersonntag 2022 Sonntag
18. Apr Ostermontag 2022 Montag
01. Mai Tag der Arbeit 2022 Sonntag
26. Mai Christi Himmelfahrt 2022 Donnerstag
05. Juni Pfingstsonntag 2022 Sonntag
06. Jun Pfingstmontag 2022 Montag
16. Jun Fronleichnam 2022 Donnerstag
03. Okt Tag der Deutschen Einheit 2022 Montag
31. Okt Reformationstag 2022 Montag
01. Nov Allerheiligen 2022 Dienstag
25. Dez 1. Weihnachtstag 2022 Sonntag
26. Dez 2. Weihnachtstag 2022 Montag

In order to visually highlight the holidays, we go here again via the “Conditional Formatting” step and then work with a VLOOKUP, which refers to the list with the holidays.

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

Note:
With the VLOOKUP used, we refer to 01.01 with B4 and the values $ O $ 4: $ O $ 34 refer to the list of public holidays in absolute terms. Depending on where you use this list, it can of course be a little different for you. You can read more about this Funktion here >>>

See fig. (Click to enlarge)

Feiertage in Excel Kalender hervorheben
Advertisement

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

In order to be able to use the calendar later dynamically for the following years, we recommend creating a list of public holidays that the calendar can refer to later. If a day is omitted later or postponed, you only need to change this relatively short list of public holidays.

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

Holidays 2022 (Germany)
01. Jan Neujahr 2022 Samstag
06. Jan Heilige Drei Könige 2022 Donnerstag
15. Apr Karfreitag 2022 Freitag
17. Apr Ostersonntag 2022 Sonntag
18. Apr Ostermontag 2022 Montag
01. Mai Tag der Arbeit 2022 Sonntag
26. Mai Christi Himmelfahrt 2022 Donnerstag
05. Juni Pfingstsonntag 2022 Sonntag
06. Jun Pfingstmontag 2022 Montag
16. Jun Fronleichnam 2022 Donnerstag
03. Okt Tag der Deutschen Einheit 2022 Montag
31. Okt Reformationstag 2022 Montag
01. Nov Allerheiligen 2022 Dienstag
25. Dez 1. Weihnachtstag 2022 Sonntag
26. Dez 2. Weihnachtstag 2022 Montag

In order to visually highlight the holidays, we go here again via the “Conditional Formatting” step and then work with a VLOOKUP, which refers to the list with the holidays.

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

Note:
With the VLOOKUP used, we refer to 01.01 with B4 and the values $ O $ 4: $ O $ 34 refer to the list of public holidays in absolute terms. Depending on where you use this list, it can of course be a little different for you. You can read more about this Funktion here >>>

See fig. (Click to enlarge)

Feiertage in Excel Kalender hervorheben
Advertisement

4. Adjust the look of the vacation calendar

4. Adjust the look of the vacation calendar

We have now basically completed the most important functions for our Excel vacation calendar and can start fine-tuning so that we can also enter vacation days etc.

First we want to format the representation of the date values ​​differently. Because specifying the year is actually superfluous. To do this, we mark all date values ​​in the calendar and go to cell formatting, select “User-defined” and enter DD. TTT a. This means that only the day of the week is displayed 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” from the context menu. The newly created columns will all be formatted with a background color that they inherited from the previous column. You can easily remove these via “Delete” & “Delete formats”.

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

See fig. (Click to enlarge)

Datumswerte in Excel formatieren
Excel Urlaubsplanung Zwischenschritt

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

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

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

Tip:
A dynamic Excel Premium Vacation Planner 2022 and a Personnel and Shift Planner 2022, which you can use and freely adapt every year, are available for download in addition to other templates. (currently only available in German Language)

Features:

  • dynamic adjustment of holidays and weekends
  • Calculation of vacation and remaining vacation days
  • Colored highlighting and drop-down selection of reasons for absence
  • Holidays can be adjusted depending on the state
  • and much more.
Blogverzeichnis Bloggerei.de

We have now basically completed the most important functions for our Excel vacation calendar and can start fine-tuning so that we can also enter vacation days etc.

First we want to format the representation of the date values ​​differently. Because specifying the year is actually superfluous. To do this, we mark all date values ​​in the calendar and go to cell formatting, select “User-defined” and enter DD. TTT a. This means that only the day of the week is displayed 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” from the context menu. The newly created columns will all be formatted with a background color that they inherited from the previous column. You can easily remove these via “Delete” & “Delete formats”.

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

See fig. (Click to enlarge)

Datumswerte in Excel formatieren
Excel Urlaubsplanung Zwischenschritt

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

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

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

Tip:
A dynamic Excel Premium Vacation Planner 2022 and a Personnel and Shift Planner 2022, which you can use and freely adapt every year, are available for download in addition to other templates. (currently only available in German Language)

Features:

  • dynamic adjustment of holidays and weekends
  • Calculation of vacation and remaining vacation days
  • Colored highlighting and drop-down selection of reasons for absence
  • Holidays can be adjusted depending on the state
  • and much more.
Blogverzeichnis Bloggerei.de

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Search the net for other topics:

About the author:

Michael W. Suhr
Michael W. SuhrWebdesigner / MBA
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:





You might also be interested in:

Popular Posts:

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen

Popular Posts:

1509, 2021

Microsoft Excel: Create vacation planner 2022 with public holidays and weekends

September 15th, 2021|Categories: Uncategorized, Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

We explain how you can create your own vacation planner 2022 in Microsoft Excel. And of course with a display of public holidays and weekends.

1409, 2021

Create annual calendar 2022 in Microsoft Excel

September 14th, 2021|Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: , , , , |

In our tutorial we describe how you can create an annual calendar for 2022 with a display of the calendar week and public holidays in Excel, and use it anew every year.

Word & Excel Templates:

See more

Word CV Templates:

See more

Monthly Bestseller:

Smart-Home

Bestseller Kategorie Smart-Home
Angebote ansehen

Homeoffice

Bestseller Kategorie Homeoffice
Angebote ansehen

Smartphone & Zubehör

Bestseller Kategorie Smartphone und Zubehoer
Angebote ansehen

Title

Most read:

Search by category:

Go to Top