The calendar week: a popular means of scheduling, but compared to the current date, you hardly know it by heart. Microsoft Office provides the CALENDAR WEEK function of the same name in its Excel calculation program. With their help, you can automatically display the week you are in and fill your worksheet with all the calendar weeks of the year in advance. We'll explain how it's done.
The CALENDAR WEEK function and its function arguments
The formula of the calendar week function is calculated in Excel as follows:
=KALENDERWOCHE(DATUM;WOCHENSTART)
For one of the two function arguments, we show you how you can automatically insert the current date in Excel. After you have done that, the "start of the week " is another important factor for the correct calculation and display of KW. Here you can adapt the display to your individual requirements. The start of the week is shown as a number when the formula is calculated. There are two basic systems of numbering.
System 1 | KW 1 = the week that includes the first of January |
System 2 | KW 1 = the week that includes the first Thursday of the year Note: This corresponds to the procedure for European week numbering specified in ISO 8601 |
System 1 | KW 1 = the week that includes the first of January |
System 2 | KW 1 = die Woche, die den ersten Donnerstag des Jahres umfasst Hinweis: Dies entpricht der in ISO 8601 festgelegten Vorgehensweise für die europäische Wochennummerierung |
If you would like to display the KW display according to the European standard (system 2), always enter 21 as the start of the week in the function formula. In order to be able to individually configure the KW display and thus adapt it to any geographical requirements, use the numerical values in the following overview:
Beginning of the week | Number to be entered at the start of the week |
Monday | 2 or 11 |
Tuesday | 12th |
Wednesday | 13th |
Thursday | 14th |
Friday | 15th |
Saturday | 16 |
Sunday | 1 or 17 or nothing |
Wochenanfang | Einzutragende Zahl bei Wochenstart |
Montag | 2 oder 11 |
Dienstag | 12 |
Mittwoch | 13 |
Donnerstag | 14 |
Freitag | 15 |
Samstag | 16 |
Sonntag | 1 oder 17 oder nichts |
Note: If you do not enter the number for the start of the week, the week will automatically start on Sunday . This automation is built into Excel by default..
How to use CALENDAR WEEK
Follow our step-by-step instructions or take a look at the brief instructions .