Using Calculated Columns to Display Weekly Items

This post details how to display the current week’s items.  Displaying current calendar events and tasks can help keep your site’s home page dynamic and fresh.

My example uses a calendar list, but this solution can be used in any library or list with a date field.  For non-calendars, substitute the “Start Time” and “End Time” columns as needed.

IMPORTANT:  Recurring calendar events will not display in the week view.

NOTE: This blog assumes you know how to complete the following actions: 1) Create lists, 2) create new list columns, 3) create views, and 4) add web parts to web pages.   The example was created using Office 365.

Calculated Columns for Displaying Weekly Items

Create the below calculated columns in your list or library.

NOTE:   If you copy the formulas below, replace all opening and closing quotation marks with regular quotation marks.  Otherwise, you will receive an error when saving the new columns.

Calculated Column

Name

Data Type Returned Formula
Calc_WeekViewEnd Date and Time/Date Only =[End Time]-WEEKDAY([End Time],2)+7
Calc_WeekViewStart Date and Time/Date Only =[Calc_WeekViewEnd]-7

 

View Settings for “This Week” Only

Create a view named “This Week” with the following filters:

Calc_WeekViewStart is less than or equal to [Today], AND, Calc_WeekViewEnd is greater than or equal to [Today]

thisweek1_viewsettings

thisweek2

Calculated Columns for Displaying the Day of the Week (or Days )

To add the day of the week the event or task is occurring, create the below calculated columns, and then add the “Day” column to the “This Week” view.

NOTE:   If you copy the formulas below, replace all opening and closing quotation marks with regular quotation marks.  Otherwise, you will receive an error when saving the new columns.

Calculated Column

Name

Data Type Returned Formula
Day_CalcStartTime Date and Time/Date Only =IF(TEXT(([End Time]-[Start Time])-TRUNC(([End Time]-[Start Time]),0),”0.000000000″)=”0.999305556″,IF([Start Time]=ROUND([Start Time],0),[Start Time],[Start Time]+1),[Start Time])
Day_Start Single line of text =TEXT(WEEKDAY([Day_CalcStartTime]),”dddd”)

 

Day_End Single line of text =TEXT(WEEKDAY([End Time]),”dddd”)

 

Day Single line of text =IF((Day_Start=Day_End),Day_Start,(Day_Start&” – “&Day_End))

thisweek3_withday

 

Additional Screenshots

thisweek4_calendaroverlap

thisweek5_homepage

 

Resources

http://blog.pentalogic.net/2012/09/complete-guide-to-filtering-sharepoint-lists-by-the-current-week/

 

 

Author:  Jenny Hersko