Documentation/Calc Functions/WEEKNUM
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
WEEKNUM
Category:
Date and Time
Summary:
Determines the week number for a date specified either by a date-time serial number or in a text string. Several week numbering systems exist and so WEEKNUM accepts an argument which can be used to select how week 1 of the year is defined and which day is taken as the first day of the week.
Syntax:
WEEKNUM(Date [; Mode])
Returns:
Returns an integer in the range [0, 53] which is the week number for the given date, numbered in accordance with the supplied Mode argument.
Arguments:
Date is the date string (in quotation marks) or a date-time serial number, or a reference to the cell containing one of those types, which is the date for which the calculation is to be carried out.
Mode is an integer which defines the first week in the year and sets the start of the week. The possible values for Mode, and their meanings, are listed in the following table. In this table the Definition of Week 1 column distinguishes between two methods for selecting week 1 in the year – one method defines week 1 as the week that includes January 1; the second method defines week 1 as the week that contains the first Thursday in the year (this is the method recommended in ISO 8601). If omitted the default value for Mode is 1.
| Mode | Definition of Week 1 | Start of Week |
|---|---|---|
| 1 | January 1 | Sunday |
| 2 | January 1 | Monday |
| 11 | January 1 | Monday |
| 12 | January 1 | Tuesday |
| 13 | January 1 | Wednesday |
| 14 | January 1 | Thursday |
| 15 | January 1 | Friday |
| 16 | January 1 | Saturday |
| 17 | January 1 | Sunday |
| 21 | First Thursday in year | Monday |
| 150 | First Thursday in year | Monday |
- If Date is not a valid date expression, then WEEKNUM reports a #VALUE! error.
- WEEKNUM ignores any time components of Date.
- If Mode is non-numeric, then WEEKNUM reports a #VALUE! error.
- If Mode is a non-integer value, then WEEKNUM truncates the value.
- If (after possible truncation) Mode is not one of the expected values enumerated above, then WEEKNUM returns an invalid argument error (Err:502).
- If interoperability with Microsoft Excel is important for your spreadsheet then avoid using the Mode value of 150, which is in ODF 1.2 but is not supported by Excel.
Additional details:
General information about Calc's Date and Time functions
 |
Note:
|
- Internal to Calc, a date/time value is handled as a real number and this is sometimes referred to by the term date-time serial number. For example, 2021-02-08 12:00:00 corresponds to the date-time serial number 44235.5. The integer part of a date-time serial number (before the decimal separator) is the number of days since a defined start date. The fractional part of a date-time serial number (after the decimal separator) is the time of day expressed as a fraction of a day. For example, 0.25 is equivalent to 06:00:00 and 0.75 is equivalent to 18:00:00.
- Calc uses 1899-12-30 as the default start date (day 0) but you can go to ▸ ▸ ▸ to select alternative start dates; however, unless you have specific requirements for compatibility with legacy spreadsheet applications, we recommend that you do not change this setting. If you copy and paste cells containing date values between different spreadsheets, check that both documents are set to use the same start date to avoid unexpected changes to the dates displayed.
- Negative date-time serial numbers correspond to dates earlier than Calc's start date. Calc calculates all dates back to 15th October 1582 using the Gregorian calendar. The day before this is 4th October 1582, and Calc uses the Julian calendar for earlier dates.
- A date-time serial number is the same as any other number, except that we usually choose to display it formatted as a date/time. You can enter a number in a cell and then change the cell's formatting to display the value as a date/time. You can also enter a date/time in a cell and then change the cell's formatting to display the date-time serial number.
- For clarity, the descriptions of the Calc functions within these wiki pages use the standard ISO 8601 format for date and time because this should be independent of your selected locale settings. The default date format on your computer may be different. When entering dates as part of formulas, slashes or dashes used as date separators may be interpreted as arithmetic operators. Therefore, dates entered in this format are not recognized as dates and result in erroneous calculations. To keep dates from being interpreted as parts of formulas either use the DATE function or place the date in quotation marks and use the ISO 8601 notation. Avoid using locale dependent date formats which may produce errors if the document is loaded under different locale settings.
- Time zone information is not used by Calc's Date & Time functions.
- If you only wish to enter two digits of the year (for example 21 instead of 2021), Calc needs to know whether you mean 2021 or 1921. Go to ▸ ▸ ▸ to view or adjust the range of years which Calc will use to interpret two-digit year values.
- Calc contains logic to decide how to treat text when encountered as an operand in an arithmetic operation or as an argument to a function that expects a number instead. In this context, unambiguous conversion is possible for ISO 8601 dates and times in their extended formats with separators. For example, the simple formula
=1 + "2021-02-08"will usually give the result2021-02-09(when formatted as a date). However, this logic may be affected by the settings on the Detailed Calculation Settings dialog, accessed by selecting ▸ ▸ ▸ and pressing the button in the Detailed Calculation Settings area. If a #VALUE! error occurs, it may be because the Generate #VALUE! error option is selected, in which case you should experiment with one of the other self-explanatory options in the Conversion from text to number drop-down.
Examples:
| Formula | Description | Returns |
|---|---|---|
| =WEEKNUM("2021-01-01") | The function returns the value 1. Since no value is supplied for the Mode argument, the default value of 1 is used. When Mode is equal to 1, January 1 is defined as being in week 1. | 1 |
| =WEEKNUM("2021-01-03"; 1) | The function returns the value 2. When Mode is equal to 1, week 1 includes January 1 and the first day of the week is Sunday. Since 2021-01-03 was a Sunday, it was the first day of the second week of the year. | 2 |
| =WEEKNUM("2021-01-01"; 21) | The function returns the value 53. When Mode is equal to 21, week 1 includes the first Thursday of the year (2021-01-07) and the first day of that week is Monday (2021-01-04). Therefore 2021-01-01 lies in the last month of 2020, which happened to be week 53 (other years may comprise only 52 weeks). | 53 |
| =WEEKNUM("2021-01-04"; 21) | The function returns the value 1. When Mode is equal to 21, week 1 includes the first Thursday of the year (2021-01-07) and the first day of that week is Monday (2021-01-04). | 1 |
=WEEKNUM(A1; B1) where cell A1 contains the date 2021-02-26 and cell B1 contains the value 13. |
The function returns the value 9. | 9 |
| =WEEKNUM(44251; 13) | The function again returns the value 9. 44251 is the date-time serial number for 2021-02-26 and so the argument values are identical to those in the previous example. | 9 |
| =WEEKNUM("2021-02-26T15:00:00"; 13.789) | The function again returns the value 9. The time components of the Date argument are ignored, and the value of the Mode argument is truncated to an integer. | 9 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
WEEKNUM