'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate an ISO Week Number in a SharePoint List
Question details
The user needs to find a reliable formula to calculate the correct ISO week number for dates in a SharePoint List, as standard calculations often fail at year boundaries.
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column in a SharePoint List to accurately display the ISO week number based on an existing date field.
- Observed behavior
- SharePoint's standard calculated columns return an incorrect ISO week number when using standard week formulas, especially for dates around the start or end of a calendar year.
Ensure you have the necessary site permissions to modify List Settings and add new calculated columns. Verify the exact name of your date column (e.g., [Date]) so you can replace it accurately within the formula.
Use the ISO Thursday-Based Formula
Implement a custom calculated column formula that adheres to the ISO 8601 standard by calculating the week based on Thursday.
SharePoint calculated columns do not support modern Excel functions like ISOWEEKNUM. To get an accurate ISO week number, you must use a mathematical formula that aligns with the ISO 8601 standard, which dictates that week 1 of any year is the week containing the first Thursday.
Open your SharePoint list, click on the gear icon in the top right corner to open Settings, and select 'List settings'.
Scroll down to the Columns section and click on 'Create column'. Give the column a name, such as 'ISO Week Number', and select 'Calculated (calculation based on other columns)' as the column type.
In the formula text box, paste the following formula: =INT(([Date]-DATE(YEAR([Date])-DAYOFWEEK([Date])+1,1,3)+WEEKDAY(DATE(YEAR([Date])-DAYOFWEEK([Date])+1,1,3))+5)/7). Make sure to replace '[Date]' with the exact name of your list's date column if it differs.
Under 'The data type returned from this formula is', select 'Number'. Set the number of decimal places to '0', then click 'OK' to save your new column.
Easily Manage and Calculate Spreadsheet Data with WPS Office
While SharePoint requires complex formulas for ISO week calculations, managing your exported lists in WPS Spreadsheet is incredibly simple. WPS Office is a free, lightweight alternative to Microsoft Office that supports modern functions natively, helping you manipulate your data faster.
- 1. Open Exported Data: Export your SharePoint list to an Excel file and open it directly in WPS Spreadsheet.
- 2. Use the ISOWEEKNUM Function: In a new column, simply type =ISOWEEKNUM(A2) (assuming A2 is your target date cell).
- 3. Apply to All Rows: Drag the fill handle down to apply the precise ISO week number calculation to your entire dataset effortlessly.
Frequently Asked Questions
Why does SharePoint return incorrect week numbers at the end of the year?
Standard week formulas calculate based on traditional calendar weeks. However, the ISO 8601 standard dictates that week 1 of a year must contain the first Thursday. This means days in late December or early January can fall into a different ISO year's week, causing standard SharePoint formulas to return mismatched numbers.
Can I use the ISOWEEKNUM function directly in a SharePoint calculated column?
No, SharePoint calculated columns do not natively support the modern ISOWEEKNUM function found in newer versions of Excel. You must use a workaround formula combining INT, DATE, YEAR, and WEEKDAY to achieve the same result.
What data type should the calculated ISO week column be set to in SharePoint?
When setting up your calculated column in the List Settings, you should select 'Number' as the data type returned from the formula and strictly set the number of decimal places to zero (0) to display a clean integer.
