Formula calculated column: show date as week number

[Gogi_Putkaradze]

I have the following issue in SharePoint List: 

I have a column name SalesWeek and I want to show in another column named "WeekNB" the week number. 

In short show 4/21/2022 in Salesweek as week 17 column WeekBN 

[#2]

@Gogi_Putkaradze you can do this with the following formula. Change Alert to Salesweek:

=INT((Alert-DATE(YEAR(Alert),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Alert),1,1)),"d")))/7)

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#3]

Hey, Ive been using this formula for a while now without issues. In 2023 the week number is off by 3 or 4 weeks. So data entered today is coming out to week #4. 12/31/2023 comes out to week #56. Im trying to find a solution that would keep the previous years data accurate and work for 2023 as well. 

=INT(([Completion Date]-DATE(YEAR([Completion Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Completion Date]),1,1)),"d")))/7)

@RobElliott 

[#4]

@Lev_Kr my network operations team was able to fix the same problem using this code. We are in SP2016, with on prem servers.

=CONCATENATE(TEXT(IF(INT(([Work date]-DATE(YEAR([Work date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Work date]),1,1)-1),"d")))/7)=0,52,INT(([Work date]-DATE(YEAR([Work date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Work date]),1,1)-1),"d")))/7)),"0"))

[#5]

Brilliant! Works like charm. I've been struggling with this one for 18 days now... Lol!!

[#6]

So happy it worked for you!

[#7]

I have the same issue as Lev, but this new formula will not work for me. It simply tells me I have an error when I try to save the formula above. We are using SP in the cloud. Any thoughts?

[#8]

Oh I wish I could help! I've not yet worked in the cloud version of SP and, honestly, barely understand what my NetOps team gave me in the calculation above. I can do some calculated columns but when it comes to complexity with dates I get lost...maybe someday I'll learn. I hope you find an answer - I know how frustrating this was to me when it first started!

[#9]

While this works, in O365 SharePoint, you cannot filter or group or create a view by the result of the calculation. Very odd!!

[#10]

Be sure to rename the field to what yours is called. I know this sounds really dumb, but I just copied and pasted the first time as well, since I was so excited. I forgot to change the column called Work day, to the column in my list that I needed it to reference.

[#11]

how come when i tried to manually input Jan 1,2024, the output workweek is 4 instead of 1?

[#12]

I am fairly certain that this is the definitive code to calculate week number.  It works with every year I throw at it. But note that this code assumes your week starts on a Sunday.  If it's starts on a Monday, it will require some adjustment. I'll post the code for Monday at the bottom. 

(For your use, replace anything that says TESTDATE, to the date you want)

=ROUNDUP((DATEDIF("1/1/"&TEXT(TESTDATE,"yyyy"),TESTDATE,"d")+IF((TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d"))="31",1,TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d")+1))/7,0)

If you want the TLDR, here is the logic for the code.

1.  Calculate the date difference between the 1/1/yyyy and the date you want calculated.  In my instance it's called TESTDATE.

DATEDIF("1/1/"&TEXT(TESTDATE,"yyyy"),TESTDATE,"d")

2.  Calculate the weekday # for 1/1/yyyy.  Sunday=31, Mon =1, Tue = 2 and etc to Sat = 6. But I need Sunday to equal 1.  So, I made an if statement.  If the weekday of 1/1/yyyy = 31, then return value of 1.  For any other weekday, it will be the value +1.  So Mon=2, Tue=3.... Sat=7. 

IF((TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d"))="31",1,TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d")+1)

3.  Add the two values above to give you an adjusted value to calculate the week.  So if for that year, the 1/1 was a Saturday.  That means the DateDif between 1/1 and 1/1 is 0.  So, I add 7. If it was Friday I add 6 because the DateDif is 1.  and so forth.  This ensure that any day in week 1 for that year will be equal to 7 or less.  And in week 2 it will be 14 or less. In week 3, 21 or less.  And so on.  

4.  Since any day in week 1 will be 7 or less.  And week 2 will be 14 or less. and so forth.  You can then divide that entire value by 7 and then round the value up.  This will give you accurate week number for any date in any year.  

Code if you want your Start of the week to be Monday. Basically the only changes that need to be made is that Sunday needs to =7.  And we no longer have to +1 to the weekday value for the other days.  Saturday should no longer be 7.  It needs to be 6.  Friday should be 5.  And etc.  

=ROUNDUP((DATEDIF("1/1/"&TEXT(TESTDATE,"yyyy"),TESTDATE,"d")+IF((TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d"))="7",1,TEXT(WEEKDAY("1/1/"&TEXT(TESTDATE,"yyyy")),"d")))/7,0)

[#13]

A 2024 update.

My weeknum equivalents all broke in 2024. This formula is however working:

=IF(ROUNDUP((ROUNDDOWN([Calendar Date],0)-(DATE(YEAR(ROUNDDOWN([Calendar Date],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Calendar Date],0)),1,1))))/7,0)>52,1,ROUNDUP((ROUNDDOWN([Calendar Date],0)-(DATE(YEAR(ROUNDDOWN([Calendar Date],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Calendar Date],0)),1,1))))/7,0))

[#14]

I found an article for the same problem in Power Automate and translated the formula from it, which seems to work (For the calculation of ISO 8601 calendar weeks). Here is the translated formula (just replace TESTDATE with your date):

=TEXT(ROUNDDOWN((DATEDIF(DATE(YEAR(TESTDATE - IF(WEEKDAY(TESTDATE)=0,6,WEEKDAY(TESTDATE)-1)+3),1,1),TESTDATE - IF(WEEKDAY(TESTDATE)=0,6,WEEKDAY(TESTDATE)-1)+3,"D")+7)/7,0),"0")

Link to the article: https://ryanmaclean365.com/2020/04/29/calculating-iso-8601-week-number-for-dates-in-power-automate/

[#15]

Following up on this topic, I tried implementing this logic into our Sharepoint online but realized, that calculated column values are static and only update, when the item is updated. As we want to use this calculated column as a basis for a filter (e.g. for showing only the list items that have their [start_date] column set to the current week), we would need to implement this with a more dynamic approach (which would be JSON I assume).
Any idea how a formula for JSON would look on a NUMBER column, that extracts the week number from another DATE column and calculates it dynamically?