# 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 17 column WeekBN

9 Replies

# Re: Formula calculated column: show date as week number

@Gogi_Putkaradze you can do this with the following formula. Change Alert to Salesweek:

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

# Re: Formula calculated column: show date as week number

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)

# Re: Formula calculated column: show date as week number

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

# Re: Formula calculated column: show date as week number

Brilliant! Works like charm. I've been struggling with this one for 18 days now... Lol!!

# Re: Formula calculated column: show date as week number

So happy it worked for you!

# Re: Formula calculated column: show date as week number

I have the same issue as Lev, but this new formula will not work for me. It simply tells me I have an error when I try to save the formula above. We are using SP in the cloud. Any thoughts?

# Re: Formula calculated column: show date as week number

Oh I wish I could help! I've not yet worked in the cloud version of SP and, honestly, barely understand what my NetOps team gave me in the calculation above. I can do some calculated columns but when it comes to complexity with dates I get lost...maybe someday I'll learn. I hope you find an answer - I know how frustrating this was to me when it first started!

# Re: Formula calculated column: show date as week number

While this works, in O365 SharePoint, you cannot filter or group or create a view by the result of the calculation. Very odd!!

# Re: Formula calculated column: show date as week number

Be sure to rename the field to what yours is called. I know this sounds really dumb, but I just copied and pasted the first time as well, since I was so excited. I forgot to change the column called Work day, to the column in my list that I needed it to reference.