Calculated field for Week Number not working for certain Sundays

Question

Calculated field for Week Number not working for certain Sundays

Amy Stevens 20

I'm using this formula in a Sharepoint calculated field to display week number in the format Year-Wk (e.g., 2023-05 for May).

=YEAR([Date of SEV Event]) & "-" & TEXT(IF(INT(([Date of SEV Event]-DATE(YEAR([Date of SEV Event]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Date of SEV Event]),1,1)-1),"DD")))/7)=0,52,INT(([Date of SEV Event]-DATE(YEAR([Date of SEV Event]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Date of SEV Event]),1,1)-1),"DD")))/7)),"00")

I've found at least 2 Sundays that show the previous week number - 2/26/23 and 1/1/23. Does anyone know how to fix that? I need this to work for dates starting in 2021 and going forward.

The Date of SEV Event column is Date only.

Thanks!

Amy

Amy Stevens 20 Reputation points

2023-05-24T18:14:05.92+00:00

It's just 1/1/23 that converts to the wrong week - and the calculation returns 2023-52.

Accepted answer

1 additional answer

Your answer

Amy Stevens 20 Reputation points

2023-05-24T18:14:05.92+00:00

It's just 1/1/23 that converts to the wrong week - and the calculation returns 2023-52.

Answer 1

Xyza Xue_MSFT 30,176 Microsoft External Staff

Hi @Amy Stevens ,

Use the following formula( it applies to every year):

=YEAR([Date of SEV Event])&"-"&ROUNDUP((INT([Date of SEV Event])-DATE(YEAR([Date of SEV Event]),1,1)+1)/7,0)

User's image

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Paez, Patrick 0 Reputation points

2024-03-06T12:51:05.8266667+00:00

Thank you, this worked best for me. Follow up issue, SharePoint is sorting all of the dates improperly with the way its formatted (YYYY-W). For example, "2024-1, 2024-10, 2024-2, 2024-3, etc. It thinks week 10 goes between week 1 and week 2, because the week is only one digit with no leading "0" at the front. Is there a way to modify the output of the formula to force a leading "0" when the week is one numeric digit (0 - 9)? This way it would sort it properly (Example; 2024-01, 2024-02, 2024-03 ... 2024-10, etc.)

Answer 2

Xyza Xue_MSFT 30,176 Microsoft External Staff

Hi @Amy Stevens ,

I improved your formula, please use the following formula:

=YEAR([Date of SEV Event])&"-"&ROUNDUP((INT([Date of SEV Event])-44926)/7,0)

Here are my test results:

User's image

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Amy Stevens 20 Reputation points

2023-05-25T13:55:11.9033333+00:00

Thanks for the formula! It works for 2023 dates but not 2022.
Amy Stevens 20 Reputation points

2023-05-30T13:49:47.7133333+00:00

Thank you! This seems to work correctly.
Paez, Patrick 0 Reputation points

2024-03-06T12:59:56.4433333+00:00

Can you say why it's an improvement over the original post? I am trying to decide which one to use.

Share via

Calculated field for Week Number not working for certain Sundays

1 additional answer

Your answer