Create vacation planner 2022 in Excel

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.

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.

Microsoft Excel: Urlaubsplaner 2022 mit Feiertagen und Wochenenden erstellen

Topic Overview

Anzeige

Create vacation planner 2022 in Excel

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.

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.

Microsoft Excel: Urlaubsplaner 2022 mit Feiertagen und Wochenenden erstellen

Topic Overview

Anzeige

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.

cshow
Ads

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
cshow
Ads

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.

cshow
Ads

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
cshow
Ads

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
cshow
Ads

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
cshow
Ads

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 for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
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:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top