Apr 21 2022 08:49 AM
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
Apr 21 2022 10:04 AM - edited Apr 21 2022 10:06 AM
@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)
Jan 04 2023 08:42 AM
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)
Jan 05 2023 04:02 PM
@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"))
Jan 18 2023 11:08 AM
Jan 20 2023 07:20 AM
Jan 30 2023 08:40 AM
Jan 30 2023 11:59 AM
Feb 10 2023 02:57 PM
Feb 10 2023 02:58 PM
Oct 10 2023 01:47 AM
Nov 29 2023 07:10 PM
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)
Jan 04 2024 03:59 AM
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))
Jan 08 2024 12:46 AM
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/
Jul 24 2024 01:22 AM