Microsoft Excel Calendar Template 2015 Download
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.
Topic Overview
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.
Amazon Sonderaktionen: X-MAS 2021
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.
Topic Overview
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.
Amazon Sonderaktionen: X-MAS 2021
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.
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)
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)
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.
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)
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)
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)
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)
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.
| |||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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)
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.
| |||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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)
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)
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.
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)
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.
Search the net for other topics:
About the author:
Search by category:
Search by keyword:
You might also be interested in:
Search the net for other topics:
About the author:
Search by category:
Search by keyword:
You might also be interested in:
Popular Posts:
Word & Excel Templates:
Word CV Templates:
Monthly Bestseller:
Amazon Sonderaktionen: X-MAS 2021
Popular Posts:
Word & Excel Templates:
Word CV Templates:
Monthly Bestseller:
Amazon Sonderaktionen: X-MAS 2021
Related Posts
Title
Source: https://ms-office-training.de/en/microsoft-excel-create-vacation-planner-2022-with-public-holidays-and-weekends
Posted by: hoytchiarmontee0194261.blogspot.com
Post a Comment for "Microsoft Excel Calendar Template 2015 Download"