Contains paid Promotion

Advertisement
Advertisement

To create a dynamic one
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
Anzeige
Xbox One X Battlefield V Gold Rush Special Edition Bundle
Xbox One X Battlefield V Gold Rush Special Edition...*
by Microsoft

Die neue Xbox One X ist die kompakteste und fortschrittlichs...

*Zuletzt aktualisiert am 15. November 2018 um 5:33 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

To create a dynamic one 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
Anzeige
Xbox One X Battlefield V Gold Rush Special Edition Bundle
Xbox One X Battlefield V Gold Rush Special Edition...*
by Microsoft

Die neue Xbox One X ist die kompakteste und fortschrittlichs...

*Zuletzt aktualisiert am 15. November 2018 um 5:33 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. The preparation

1. The preparation

In order to be able to create the calendar later using Excel’s copy control with just 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 with each other using the “connect and center” function.
  • Enter the month “January” in the first cell grouping.
  • Highlight the cell in which “January” appears, and copy it over to the right until December.

See picture: (click to enlarge)

Excel Kalender 2019 Pic1

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 Excel’s copy control with just 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 with each other using the “connect and center” function.
  • Enter the month “January” in the first cell grouping.
  • Highlight the cell in which “January” appears, and copy it over to the right until December.

See picture:

Excel Kalender 2019 Pic1

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. Enter the important functions

2. Enter 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, refer back 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 – day of the week – date under the month of February, and copy them 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 February, and hide with WENNFEHLER again unwanted error messages.

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

Now you can mark the cell with the 02.01, copy it at the right bottom 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.

kautionsfrei.de
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, refer back 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 – day of the week – date under the month of February, and copy them 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 February, and hide with WENNFEHLER again unwanted error messages.

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

Now you can mark the cell with the 02.01, copy it at the right bottom 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.

kautionsfrei.de
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 in the Ribbon and choose Use Formula to Determine Cells to Format.
  • Enter the following function here: = WEEKDAY (C5; 2)> 5
  • Now click on “Format” and choose either a background color or font color as desired.
  • Confirm the rule with “OK”

See picture: (click to enlarge)

Excel Kalender 2019 Bedingte Formatierung
Anzeige
Fire TV Stick 4K mit Alexa-Sprachfernbedienung + Echo Dot (3. Generation)
List Price: € 119.98 You Save: € 25.00 (21%) Prime Price: € 94.98 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 15. November 2018 um 8:17 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

The existing marking of the cells can be left marked the same way, and at the lower right edge you can touch them 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 now, all you have to do is change the year, and all other values in the calendar will adapt dynamically.

See picture (click to enlarge)

Excel Kalender 2019 Pic11
kautionsfrei.de
Anzeige

If the whole thing is a bit too complex for you to start with, we have prepared a calendar for you to download here for free. 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 in the Ribbon and choose Use Formula to Determine Cells to Format.
  • Enter the following function here: = WEEKDAY (C5; 2)> 5
  • Now click on “Format” and choose either a background color or font color as desired.
  • Confirm the rule with “OK”

See picture:

Excel Kalender 2019 Bedingte Formatierung
Anzeige
Fire TV Stick 4K mit Alexa-Sprachfernbedienung + Echo Dot (3. Generation)
List Price: € 119.98 You Save: € 25.00 (21%) Prime Price: € 94.98 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 15. November 2018 um 8:17 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

The existing marking of the cells can be left marked the same way, and at the lower right edge you can touch them 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 now, all you have to do is change the year, and all other values in the calendar will adapt dynamically.

See picture:

Excel Kalender 2019 Pic11
kautionsfrei.de
Anzeige

If the whole thing is a bit too complex for you to start with, we have prepared a calendar for you to download here for free. 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

friendsurance.de
Anzeige
Blogverzeichnis Bloggerei.de

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
LG G7 ThinQ Smartphone (15,47 cm (6,1 Zoll) FullVision LCD Display, 64GB interner Speicher, 4GB RAM, Einstellbare Notch, IP68, MIL-STD-810G, Android 8.0) Schwarz
List Price: € 459.00 You Save: € 35.00 (8%)  Price: € 424.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 14. November 2018 um 22:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

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
LG G7 ThinQ Smartphone (15,47 cm (6,1 Zoll) FullVision LCD Display, 64GB interner Speicher, 4GB RAM, Einstellbare Notch, IP68, MIL-STD-810G, Android 8.0) Schwarz
List Price: € 459.00 You Save: € 35.00 (8%)  Price: € 424.00 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 14. November 2018 um 22:22 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-10-27T10:59:25+00:00By |Categories: Excel 2016/2019, Office 2016/2019|Tags: , , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok