Excel: Create a dynamic calendar

In Microsoft Excel's dynamic calendar, you can choose any year for which you can display the correct days of the week for each month. If you change the year, the daily date values ​​change accordingly. You can read how to create such a flexible calendar manually in our practical tip.

How to create a dynamic calendar in Excel

1. Enter the word "Year" in cell A1 and to the right in B1 any year, such as 2050. (see screenshot)
2. If the days should start in cell B2, enter the following formula here: "= DATE (B1; 1; 1)". The date value is made up of the year specified in cell B1, the first month and the New Year's day of the desired year. Once the formula has been entered, you must press the Enter key to confirm. (see screenshot picture 1)
3. New Year's Day now serves as the starting point for all other days. The formula "= B2 + 1" is now written in cell B3 below. So simply add a tag to the content of cell B2 and display it in B3. (see screenshot picture 2)
4. Now duplicate this formula by selecting cell B3 and dragging the small cross that appears at the bottom right of the cell further down in the column. Excel copies the formula and adjusts the references independently, so that a chain of daily additions is created. (see screenshot picture 3)

If you also want a day of the week to be displayed for the date, you can do this by changing the cell format to "Date, long" in the "Start" tab. (see screenshot)

Format the days of the week on the calendar

In order to keep the overview in the calendar, it makes sense to have the days of the week marked in color. For example, you can save the weekends with a desired color. So that this can be done quickly, Excel has a special formula:

• In the example calendar, all days start from cell B2. Select all cells in which the days are entered.
• In the "Start" tab, select the "Conditional formatting" button (see screenshot) and then go to "New rule". Under "Use formula to determine the cells to be formatted" you must enter the rule "= day of the week (B2; 2)> 5". Parameter 2 shows Excel that counting should begin on Monday, since the program assigns Sunday the value 1, Monday the value 2, and so on. B2 indicates that Excel should start formatting from New Year's Day, the first day of your calendar.
• Now click on "Format ...", for example to define a background or font color for the weekend days.
• Confirm the new formatting rule with "OK".
• You can also find more about conditional formatting in Excel on this page.

By the way, we already have the right template for a dynamic calendar available for download, which should make things easier for you. You can also have holidays marked, school holidays entered and the entire calendar formatted to your taste.

This guide article refers to MS Excel 2010.