Weeknumber when week starts on Sunday

[Rapaille]

Hello,

I'm using below formula to get the week number in a SharePoint list column. In this formula the week starts on Monday. How can I get the week started on Sunday?

=CONCATENATE(TEXT(IF(INT((Productiedatum-DATE(YEAR(Productiedatum);1;1)+(TEXT(WEEKDAY(DATE(YEAR(Productiedatum);1;1)-1);"d")))/7)=0;52;INT((Productiedatum-DATE(YEAR(Productiedatum);1;1)+(TEXT(WEEKDAY(DATE(YEAR(Productiedatum);1;1)-1);"d")))/7));"0"))

Kind regards,

Hans

[#2]

Hi @Rapaille,

to get a week number but starting from sunday, you can try this formula.

=CONCATENATE(TEXT(IF(INT((Productiedatum-DATE(YEAR(Productiedatum),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Productiedatum),1,1),1),"d")))/7)=0,52,INT((Productiedatum-DATE(YEAR(Productiedatum),1,1)+(TEXT(WEEKDAY(DATE(YEAR(Productiedatum),1,1),1),"d")))/7));"0"))

Here's what the formula does:

- It takes your date, which is in the "Productiedatum" column.
- It figures out how many days have passed since the beginning of the year (January 1st).
-  Then, it checks if this number is zero (meaning the date is still in the previous year's week 52). If it's zero, it assigns the week number as 52.
- If it's not zero, it calculates the week number based on the number of days passed and divides it by 7 to get the week number.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic

[#3]

Hi Leon,

Thank you for your reply. I tried your formula and I get week number 41 for 17-Sep-23. It should be 38.