How to get Week number from Date in SharePoint Online?

In SharePoint Online, we know the functionality of calculated columns. Whenever we need to do some calculation based on existing columns in a list we use Calculated column.

Microsoft has given very good reference for all formulas in calculated column. And trust me date column is very intriguing feature in SharePoint list.

Today, I will show you how to calculate Week number from given date in SharePoint list.

Please use this formula-

=CONCATENATE(“Week :”,TEXT(IF(INT(([Program Enrollment Date]-DATE(YEAR([Program Enrollment Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Program Enrollment Date]),1,1)-1),”d”)))/7)=0,52,INT(([Program Enrollment Date]-DATE(YEAR([Program Enrollment Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Program Enrollment Date]),1,1)-1),”d”)))/7)),”0″))

Here I am calculating week number based on my date column ([Program Enrollment Date]). you can use your date column and most important part is that return type of your calculated column should be “Single line of text“.

Output:

Additional inputs: Based on Week number you can calculate duration of your SharePoint line items. Suppose you want to calculate duration in weeks. In this case just replace [Program Enrollment Date] by Today() in above formula.

TEXT(IF(INT((Today()-DATE(YEAR(Today()),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Today()),1,1)-1),”d”)))/7)=0,52,INT((Today()-DATE(YEAR(Today()),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Today()),1,1)-1),”d”)))/7)),”0″)

To find difference-

=CONCATENATE((TEXT(IF(INT((Today()-DATE(YEAR(Today()),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Today()),1,1)-1),”d”)))/7)=0,52,INT((Today()-DATE(YEAR(Today()),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Today()),1,1)-1),”d”)))/7)),”0″))–(TEXT(IF(INT(([Program Enrollment Date]-DATE(YEAR([Program Enrollment Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Program Enrollment Date]),1,1)-1),”d”)))/7)=0,52,INT(([Program Enrollment Date]-DATE(YEAR([Program Enrollment Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Program Enrollment Date]),1,1)-1),”d”)))/7)),”0″)),” Weeks”)

– Gopenly Team

Tags: Calculated ColumnSharePointSPO