Tuesday, November 13, 2012

Calculate Weeks In Sharepoint

=INT((Created-DATE(YEAR(Created),1,6)+(TEXT(WEEKDAY(DATE(YEAR(Created),0,1)),"d")))/7)+1

This is a modified version of this formula which didn't give me the correct week number (maybe due to regional settings?): http://lamahashim.blogspot.com/2009/10/sharepoint-calculated-field-week-number.html 

It matches the number of weeks calculated in excel and all the calculators I could find on the internet.

Update: I also needed to add a 0 before the "Week" if the number is less than 10, because it will cause an error in sorting otherwise.

Here's the formula to add a 0 if the number is less than 10:
=IF((INT((Date-DATE(YEAR(Date),1,6)+(TEXT(WEEKDAY(DATE(YEAR(Date),0,1)),"d")))/7)+1)<10,"0"&(INT((Date-DATE(YEAR(Date),1,6)+(TEXT(WEEKDAY(DATE(YEAR(Date),0,1)),"d")))/7)+1),(INT((Date-DATE(YEAR(Date),1,6)+(TEXT(WEEKDAY(DATE(YEAR(Date),0,1)),"d")))/7)+1))&" "

(Note: the &" " part forces it to be a string rather than a floating point value)

No comments:

Post a Comment