In order to group per week in a Sharepoint list, I have used the following function:
=IF([Completed date];YEAR([Completed date])&"/"&IF((INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1)>10;INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1;"0"&INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1);"Uncompleted")
Actually the code to determine the weeknumber is:
=INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1
So actually what I did was to first detect if the [Completed date] is valid (not empty), if it is it will show the text uncompleted. The next step was to check whether the week number is 2 digits if not then we will add an additional 0 to it, in order to make sure we can sort it correctly.
2 comments:
For the correct weeknumber, take away +1 in the end if the first day of the year starts at a friday to sunday (like 2010 starts in a friday) / C Stahl
=IF([Added On];YEAR([Added On])&"/"&IF((INT(([Added On]-DATE(YEAR([Added On]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Added On]);1;1)-1);"d")))/7))>10;INT(([Added On]-DATE(YEAR([Added On]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Added On]);1;1)-1);"d")))/7);"0"&INT(([Added On]-DATE(YEAR([Added On]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Added On]);1;1)-1);"d")))/7));"Uncompleted")
It's good for European :)
Post a Comment