Formula calculated column: show date as week number

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)

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)

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))

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/