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.

=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

• ### Lwanga Allan

Hello, Formula seems not to be supported in sharepoint online.
Does it still work for you?

• ### Hardesh Kumar

Yes it is working on SharePoint Online. I used this on SharePoint online. are you using correct formula?.

• ### Lwanga Allan

Hello, formula seems to return errors with custom date and today().
Does it still work?

Awesome!

• ### Hardesh Kumar

Thanks Dimos.