Contains paid Promotion

Advertisement
Advertisement

Create a Dynamic Annual calendar 2019 with Excel

Even though you can use all sorts of online calendars everywhere today, there is still a legitimate need to have a local calendar on your PC.

Of course, Excel is very well suited for this, because here you can equip the calendar with a whole range of functions, as well as freely design the layout.

In our article we would like to explain how you can use Excel to create a dynamic calendar that dynamically adjusts each year only by changing the year number, and also automatically displays the calendar week as well as the weekends.

Create dynamic calendar 2019 with Excel
Die neue Ring Stick Up Cam Battery HD-Sicherheitskamera...*
Die neue Ring Stick Up Cam Battery HD-Sicherheitskamera mit Gegensprechfunktion, Weiß, funktioniert mit Alexa
Price: € 199.00 Prime
zum Angebot*
Price incl. VAT., Excl. Shipping
*Preise zuletzt aktualisiert am 25. April 2019 um 5:56 . Alle Angaben ohne Gewähr.
Anzeige

Create a Dynamic Annual calendar 2019 with Excel

Even though you can use all sorts of online calendars everywhere today, there is still a legitimate need to have a local calendar on your PC.

Of course, Excel is very well suited for this, because here you can equip the calendar with a whole range of functions, as well as freely design the layout.

In our article we would like to explain how you can use Excel to create a dynamic calendar that dynamically adjusts each year only by changing the year number, and also automatically displays the calendar week as well as the weekends.

Create dynamic calendar 2019 with Excel
Die neue Ring Stick Up Cam Battery HD-Sicherheitskamera...*
Die neue Ring Stick Up Cam Battery HD-Sicherheitskamera mit Gegensprechfunktion, Weiß, funktioniert mit Alexa
Price: € 199.00 Prime
zum Angebot*
Price incl. VAT., Excl. Shipping
*Preise zuletzt aktualisiert am 25. April 2019 um 5:56 . Alle Angaben ohne Gewähr.
Anzeige

1. The Preparation

1. The Preparation

In order to be able to create the calendar later using the copy control of Excel with a few steps for the whole year, a few operations with the corresponding functions are necessary.

  • Enter any year in the top line.
  • Leave 2 to 3 lines underneath.
  • Connect 3 cells together using the “connect and center” function.
  • Enter the month “January” in the first cell grouping.
  • Highlight the cell that says “January” and copy it over to the right until December.

See picture: (click to enlarge)

Excel Kalender 2019 Pic1
Santander BestCredit Ratenkredit 300x250
Anzeige

Next, enter under the line in which the months are: KW – day of the week – date

You only have to do this with the first three cells under the month of January. Then mark these three cells and copy them back to the right until the month of December.

See picture: (click to enlarge)

Excel Kalender 2019 Pic2

So prepared, we can already begin to enter the first small function, and format the cells accordingly.

  • Enter the following in the cell under “Date”: = DATE (A1; 1; 1) and confirm with Enter.
  • Click on “Date” and “Custom” in this cell in the context menu or via the ribbon “Format cells”.
  • In the custom formatting box, type TT.MM. on to display only the day and the month.
  • In the cell to the left of the date thus created, enter = D5, referring to the date cell.
  • Then format this cell as Custom Date again and carry TTT there. to display only the day of the week in abbreviated form.

See picture: (click to enlarge)

Excel Kalender 2019 Pic3
Excel Kalender 2019 Pic4.1
Excel Kalender 2019 Pic4.2

In order to be able to create the calendar later using the copy control of Excel with a few steps for the whole year, a few operations with the corresponding functions are necessary.

  • Enter any year in the top line.
  • Leave 2 to 3 lines underneath.
  • Connect 3 cells together using the “connect and center” function.
  • Enter the month “January” in the first cell grouping.
  • Highlight the cell that says “January” and copy it over to the right until December.

See picture:

Excel Kalender 2019 Pic1
Santander BestCredit Ratenkredit 300x250
Anzeige

Next, enter under the line in which the months are: KW – day of the week – date

You only have to do this with the first three cells under the month of January. Then mark these three cells and copy them back to the right until the month of December.

See picture:

Excel Kalender 2019 Pic2

So prepared, we can already begin to enter the first small function, and format the cells accordingly.

  • Enter the following in the cell under “Date”: = DATE (A1; 1; 1) and confirm with Enter.
  • Click on “Date” and “Custom” in this cell in the context menu or via the ribbon “Format cells”.
  • In the custom formatting box, type TT.MM. on to display only the day and the month.
  • In the cell to the left of the date thus created, enter = D5, referring to the date cell.
  • Then format this cell as Custom Date again and carry TTT there. to display only the day of the week in abbreviated form.

See picture:

Excel Kalender 2019 Pic3
Excel Kalender 2019 Pic4.1
Excel Kalender 2019 Pic4.2
Anzeige

2. Entering the important functions

2. Entering the important functions

Now we can start with the more extensive functions.

For this we start first with the date determination for the month of February:

  • Enter the following in the cell under Date for February: = EDAM (D5; 1) The reference to the January date cell is important.
  • In the cell where the day of the week is, you refer again to the date, and format this cell as in January.
  • Now mark the cell under “KW” in the month of January and enter the following: = FAILURE (CALENDAR WEEK (D5; 2); “”)
  • Repeat the process for the cell under “KW” in the month of February.

Note:
The “FAILURE” function will later help to hide errors that do not affect the way the calendar works – but look ugly.

See picture: (click to enlarge)

Excel Kalender 2019 Pic6
Excel Kalender 2019 Pic5
Excel Kalender 2019 Pic7

Now mark the 3 cells: KW – weekday – date under the month of February, and copy them over to the right until December.

See picture: (click to enlarge)

Excel Kalender 2019 Pic9
300x250_PlatinumDouble
Anzeige

Next, enter the following function in the cell under 01.01: = FAILURE (IF (MONTH (D5 + 1) = MONTH (D $ 5); D5 + 1; “”); “”))

With this function we say Excel in short that in the month of January does not appear at once a date from the month of February, and fade with IFFAILURE again unwanted error messages.

Repeat this function in the cell under the 01.02 again. But make sure that you also refer to the date in February, and not in January.

Now you can mark the cell with the 02.01, copy this at the lower right corner of the marker until the last day of January with the left mouse button pressed down. Then mark the first cell under “Day of the week” for January, and double-click on the left at the bottom right of the selection.

This will automatically fill all cells to the end.

Anzeige

Now we can start with the more extensive functions.

For this we start first with the date determination for the month of February:

  • Enter the following in the cell under Date for February: = EDAM (D5; 1) The reference to the January date cell is important.
  • In the cell where the day of the week is, you refer again to the date, and format this cell as in January.
  • Now mark the cell under “KW” in the month of January and enter the following: = FAILURE (CALENDAR WEEK (D5; 2); “”)
  • Repeat the process for the cell under “KW” in the month of February.

Note:
The “FAILURE” function will later help to hide errors that do not affect the way the calendar works – but look ugly.

See picture:

Excel Kalender 2019 Pic6
Excel Kalender 2019 Pic5
Excel Kalender 2019 Pic7

Now mark the 3 cells: KW – weekday – date under the month of February, and copy them over to the right until December.

See picture:

Excel Kalender 2019 Pic9
300x250_PlatinumDouble
Anzeige

Next, enter the following function in the cell under 01.01: = FAILURE (IF (MONTH (D5 + 1) = MONTH (D $ 5); D5 + 1; “”); “”))

With this function we say Excel in short that in the month of January does not appear at once a date from the month of February, and fade with IFFAILURE again unwanted error messages.

Repeat this function in the cell under the 01.02 again. But make sure that you also refer to the date in February, and not in January.

Now you can mark the cell with the 02.01, copy this at the lower right corner of the marker until the last day of January with the left mouse button pressed down. Then mark the first cell under “Day of the week” for January, and double-click on the left at the bottom right of the selection.

This will automatically fill all cells to the end.

Anzeige

3. Highlight weekends in the calendar

3. Highlight weekends in the calendar

Now the calendar is actually ready so far that you could finish it with a copy step.

However, for our example we want to let the weekends be emphasized automatically, and copy this function as well.

  • Select the first 3 cells below the month of January, and expand the selection until the end of the month.
  • Then click Conditional Formatting on the Ribbon and choose Use formula to find the cells you want to format.
  • Enter the following function here: = WEEKDAY (C5; 2)> 5
  • Now click on “Format” and select either a background color or font color as desired.
  • Confirm the rule with “OK”

See picture: (click to enlarge)

Excel Kalender 2019 Bedingte Formatierung
kautionsfrei.de
Anzeige

You can leave the existing marking of the cells the same way, and touch them at the lower right edge with the left mouse button, and copy them to the right until the end of December.

And that would be our calendar then already finished (at least in the basic structure). If you want to jump to another year, all you have to do is change the year, and all the other values in the calendar will dynamically adjust.

See picture: (click to enlarge)

Excel Kalender 2019 Pic11
Anzeige

If the whole thing is a bit too complex for you to start with, we have prepared a calendar and have it made available for free download here. You can customize the calendar and look at the functions again.

Download Excel Annual Calendar 2019

Now the calendar is actually ready so far that you could finish it with a copy step.

However, for our example we want to let the weekends be emphasized automatically, and copy this function as well.

  • Select the first 3 cells below the month of January, and expand the selection until the end of the month.
  • Then click Conditional Formatting on the Ribbon and choose Use formula to find the cells you want to format.
  • Enter the following function here: = WEEKDAY (C5; 2)> 5
  • Now click on “Format” and select either a background color or font color as desired.
  • Confirm the rule with “OK”

See picture:

Excel Kalender 2019 Bedingte Formatierung
kautionsfrei.de
Anzeige

You can leave the existing marking of the cells the same way, and touch them at the lower right edge with the left mouse button, and copy them to the right until the end of December.

And that would be our calendar then already finished (at least in the basic structure). If you want to jump to another year, all you have to do is change the year, and all the other values in the calendar will dynamically adjust.

See picture:

Excel Kalender 2019 Pic11
Anzeige

If the whole thing is a bit too complex for you to start with, we have prepared a calendar and have it made available for free download here. You can customize the calendar and look at the functions again.

Download Excel Annual Calendar 2019
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Santander BestCredit Ratenkredit 728x90
Anzeige
Blogverzeichnis Bloggerei.de

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