Question

Excel: insert calendar week - this function works

Answer 1

The CALENDAR WEEK function and its function arguments
How to use CALENDAR WEEK
1st step:
2nd step:
3rd step:
4th step:
5th step:
quick start Guide

---

The Excel function CALENDAR WEEK saves you having to switch to a calendar app. We'll show you how to use them..

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
• How to use CALENDAR WEEK

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 .

1st step:

Open Excel and first enter the date over a period of your choice in any column of your worksheet .

2nd step:

Then click in the cell where you want to list the calendar week. Go to the " Formulas " tab and click on " Insert function ".

3rd step:

In the newly opened window you can then enter " Calendar week " under " Search for a function " and press the [Enter] key to confirm . By pressing it again , or click " OK " to select the function.

4th step:

In the " Function arguments " window, select the cell in which the date is located for the field next to " Consecutive_Number " . For the " Number_Type " field, enter the numerical value described above for the beginning of the week (in Europe: " 21 ") and then press [Enter] or click " OK ".

Note: You can also manually enter " = CALENDAR WEEK (B2; 21) " in the function bar .

5th step:

The result is automatically inserted into the cell. To check, you can take a look at the function bar. Then you don't have to re-enter the function every time, you can also drag down the small square in the lower right corner . Excel also automatically adjusts the values ​​here so that they relate to the date of the corresponding row.

quick start Guide

1. First fill a column of your Excel sheet with the date .
2. Click in the cell in which you want to insert the KW and then switch to the " Formulas " tab . Go there to " Insert formula ".
3. Enter " calendar week " in the search field and confirm with [Enter] or a click on " OK ". Press or click again to insert the function.
4. Select for the " Fortlaufende_Zahl " the cell that contains the date , and the " Zahl_Typ " the " 21 " in Europe or the numeric value for the week at the beginning of the week. Finally click on " OK ".

---