Update: There was a mistake in the IsInCurrentWeek formula (ignored the year….), the updated version is now here.
Recently a colleague asked me for some modifications on one of her calendars. Basically, she wanted to display only the events of the current week in the corresponding web part.
In order to get the default web part to do this, I had to find out whether a calendar entry is in the current week or not. At http://blogs.microsoft.co.il/blogs/sps/archive/2008/06/26/how-to-calculate-week-number-by-sharepoint-formula.aspx, I found the necessary information to calculate the week number of a given day.
What I did was to create 4 calculated columns for the calendar, namely WeekNumberStart, WeekNumberEnd, CurrentWeek, and IsInCurrentWeek. WeekNumberStart and WeekNumberEnd calculate the week number for the start day and end day of a calendar. IsInCurrentWeek returns a string (“Yes”, “No”) by comparing the current week number from CurrentWeek with the one from WeekNumberStart and WeekNumberEnd, and also checking if it lies in between these two.
CurrentWeek itself is actually a redundant value, as it is the same for all entries. So instead of using this extra column, the calculation could’ve also been used in the IsInCurrentWeek directly. I chose to create the extra column to make the calculations more readable at the expense of the redundancy.
The two calculated columns WeekNumberStart and WeekNumberEnd are calculated with the following formular (Replace Start Time with End Time for WeekNumberEnd):
=INT(([Start Time]-DATE(YEAR([Start Time]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Start Time]),1,1)),"d")))/7)+1
This calculated column returns a number, namely the actual week number of the given day.
The same calculation is done for CurrentWeek, but with SharePoint’s built-in Today as the given day:
As SharePoint doesn’t allow to use Today in a calculated column, I had to create a single line of text column called Today first before I could create the CurrentWeek column. Afterwards, it could be deleted again.
Lastly, IsInCurrentWeek is used to check if the WeekNumberStart or WeekNumber end are equal to CurrentWeek, or if CurrentWeek falls between these two.
=IF(OR(AND(YEAR([Start Time])<Year(Today),YEAR([End Time])>Year(Today)),OR(YEAR([Start Time])=Year(Today),YEAR([End Time])=Year(Today))), IF(WeekNumberStart=CurrentWeek,"Yes",IF(WeekNumberEnd=CurrentWeek,"Yes", IF(WeekNumberStart<CurrentWeek,IF(WeekNumberEnd>CurrentWeek,"Yes","No"),"No"))) , "No")
On the web part to display the calendar, the filter settings are updated to IsInCurrentWeek Equals to Yes
The final result: