Forum Discussion
Formula calculated column: show date as week number
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 ...
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/