SharePoint Document Issues

How to Calculate a Week Number in a SharePoint Calculated Column

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to extract the week number from date values in a SharePoint list using a calculated column, similar to how it is done in Excel.

Product: SharePoint
Device & OS: not provided
Scenario: Creating a calculated column in a SharePoint list to display week numbers based on existing date records.
Observed behavior: The user requires a specific, SharePoint-compatible mathematical formula to return the week number, as standard Excel functions are not fully supported.

Before you start

Ensure you have the exact name of your reference Date column in your SharePoint list and possess the necessary permissions to edit list settings and add calculated columns.

Solution 1Recommended

Use a SharePoint-Compatible Week Number Formula

Apply a mathematical formula in your SharePoint settings to calculate the week number based on your existing date column.

Because SharePoint does not natively support the WEEKNUM function found in Excel, you must use a combination of INT, YEAR, DATE, and WEEKDAY functions to calculate the number of weeks that have passed since the beginning of the year.

Navigate to List Settings

Open your SharePoint list, click the settings gear icon in the top right corner, and select 'List settings'.

Create a New Column

Scroll down to the 'Columns' section and click 'Create column'. Enter a recognizable name, such as 'Week Number'.

Select Calculated Column Type

Choose 'Calculated (calculation based on other columns)' as the column type for your new field.

Enter the Formula

In the Formula box, enter the week calculation formula: =INT(([DateColumnName]-DATE(YEAR([DateColumnName]),1,1)+(7-WEEKDAY([DateColumnName])))/7)+1. Be sure to replace [DateColumnName] with the exact name of your date column.

Save the Column

Set the data type returned from this formula to 'Number' with 0 decimal places, then click 'OK' to save.

Formula Syntax Note: Ensure your date column name is correctly enclosed in square brackets. If your column name is simply 'Date', you would use [Date].

Solution 2

Verify Column References and Syntax

Check for common syntax errors and ensure the date column is referenced correctly in your calculation to avoid error messages.

Free Microsoft Office alternative

Use WPS Spreadsheet for Easier Data Analysis

While SharePoint requires complex formulas for week numbers, exporting your list data to WPS Spreadsheet lets you use simple, built-in functions like WEEKNUM() and ISOWEEKNUM(). WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office for all your document and data analysis needs.

1. Export List Data: Export your SharePoint list to a CSV or Excel file format directly from your browser.
2. Open in WPS Spreadsheet: Launch WPS Office and open your downloaded file.
3. Apply WEEKNUM Function: Create a new column and type =WEEKNUM(A2) to instantly get the week number, bypassing complex formulas.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Easily calculate week numbers and manipulate dates using familiar built-in functions.Lightweight, fast, and completely free to use on Windows, Mac, and Linux.Familiar tabbed user interface makes switching from Microsoft Office completely seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the standard Excel WEEKNUM function in a SharePoint calculated column?

No, SharePoint calculated columns do not natively support the WEEKNUM or ISOWEEKNUM functions found in Excel. You must use a mathematical formula combining INT, DATE, YEAR, and WEEKDAY functions to calculate the week manually.

Why does my SharePoint calculated column show a syntax error?

Syntax errors typically occur if the column name referenced in your formula does not exactly match the actual date column name, or if your formula is missing brackets or commas. Verify that your column name is perfectly spelled and enclosed in square brackets [Column Name].

Does the manual week number formula adjust for leap years?

Yes. As long as the formula utilizes SharePoint's built-in DATE and YEAR functions, it will automatically account for leap years when calculating the number of days that have passed since January 1st.

Document & File Issues