# Use the calculated field to show weeknumber in SharePoint 2010

December 9, 2011 13 Comments

A common issue I’ve encountered is how to use the week number of a date in SharePoint. The most common use of the week number within SharePoint is to filter views or grouping of information based on the current date. Also today we were struggling again with some weeknumber code for SharePoint which was not returning the valid weeks for the standard used in the Netherlands.

In Europe week 1 starts on the Monday of the week in which 4th January falls. In other words, week 1 starts on the Monday of the week that contains the first Thursday of the year. This can be represented by the following formula:

=INT(([datecolumn]-DATE(YEAR([datecolumn]-WEEKDAY([datecolumn]-1)+4);1;3)+ WEEKDAY(DATE(YEAR([datecolumn]-WEEKDAY(datecolumn-1)+4);1;3))+5)/7)

Which is something like

- Calculate the distance between the beginning of the year (with regard of the start of week 1) to the datecolumn
- Add the day of the week of the 1st January of the datecolumn year added with 5 (Thursday)
- Divide by number of days in a week (usually 7 😉
- Floor by doing a cast to INT

When week 1 starts in the week of the 1st January (USA Standards) you can use the following code.

=INT(([datecolumn]-DATE(YEAR([datecolumn]),1,1)+ (TEXT(WEEKDAY(DATE(YEAR([datecolumn]),1,1)),"d")))/7)+1

Which is something like

- Calculate the distance between the beginning of the year to the datecolumn
- Add day of the week of the 1st January of the datecolumn year
- Add divide by number of days in a week (usually 7 😉
- Cast to integer (is Floor)
- Add one day

Beside the use of the weeknumber based on a date column, you probably want to use the current date within your formula. To use the [Today] variable in a calculated column you need to use the following workaround. Without using this workaround you will get the error that you can’t use variables like Today and Me in a calculated column.

- Add a column named Today to the list where you want to use the variable
- Add a calculated column with the weeknumber calculation using the Today value (which will initially be the column value of Today)
- Remove the column named Today

Every time you want to change the calculated column you need to repeat the steps in the same order. Good luck!

Your code worked all the way up to 1/1/17 which falls on a Sunday and the result is 5.

Hi Jason,

Is this regarding the European calculation or for calendars in the USA Standard?

Kind regards

Not a through test, but formula fails at 01-01-2012 (European calendar).

Formula returns 52, where it should be 1.

Disregard my post – 52 is actually correct 🙂 Since week 1 where the 4th of jan is…

For some reason when I do this I get a server error… any ideas?

The last datecolumn in formula is missing square brackets so if you replace with your own column name that has spaces it will fail

Depending on your configuration (locale) you will need to user ; or , in the expression of the calculated column. I’am not sure if this is this solution of your problem.

Hi….Nice article …how can i calculate the week no for a year which starts from july 1st….i need to calculate the week no for my company trading year which it starts from july 1st 2013 to june 2014 ending ……….Please give me a reply on this….

Hi..thanks for the article, its very useful. i am wondering if you could help me with reverse formula, i mean weeknum to date calculation. it will really help me save my day…

regards,

SA

You can probably perform this by using, calculation is valid only for the current year.

=[weeknum]*7+DATE(YEAR([Today]), 1,1)

How would you nest your calculation in an IF Statement. I’m trying to return, if Manual Week Field is blank, show the week number or else 0. I’m using the below calculation, seem to get syntax errors:

=IF(ISBLANK ([Manual Week]), INT((Created-DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3)+WEEKDAY(DATE(YEAR(Created-WEEKDAY(Created-1)+4),1,3))+5)/7),0)

I have resolved this now, it appears the spaces were what was causing the issue.

Hi, Thank you for the post. It helped me out a great deal, almost used the US version of the formula, until I stumbled upon your formula.

For anyone out there who gets internal server errors or “something went wrong”, try translating the formulas. In the end Sharepoint translated them automatically, but I still got the errors. DATE became DATUM and INT became INTEGER etc.