Guide | Pentalogic Technology

Posts Tagged ‘Guide’

Complete guide to filtering SharePoint lists by the current week.

Date:September 17th, 2012 Author:Ryan Tags: Calculated Columns, Guide, Tip
Category: Calculated Columns, Filter, SharePoint Ideas Comments:7 ;

A common requirement in SharePoint is to filter lists so you’re only showing the current week, month, quarter etc. If you only want to see the last X number of days (e.g. last 30 days) then it’s simple enough.

If you want setup a SharePoint view to see the current calendar week then its a little more complex – especially if you define your week differently from the standard Sunday to Saturday or Monday to Sunday.

First you must look at and really understand the method for setting up SharePoint views to filter the current calendar month or week as it’s not really obvious.

Long story short – here are the steps :-

Use one of the calculated column formula below to find the “Start of week“

Use a calculated column formula of “End of Week“

        = [Start of Week] + 6

Setup a View Filter showing items when [Today] is in that range.

        Start of Week <= [Today] AND End Of Week >= [Today]

Again – see this post for showing the method in step-by-step detail.

If your week goes Sunday to Saturday or Monday to Sunday then use :-

	Start of week	End of week
Sunday to Saturday	=[Due Date] - WEEKDAY([Due Date]) +1	=[Start of Week] + 6
Monday to Sunday	=[Due Date] – WEEKDAY([mydate],2) + 1	=[Start of Week] + 6

Obviously you will replace [Due Date] with your own particular date field.(Check out the WEEKDAY function to understand what the 2nd argument does)

The other options are a bit more complex to find the start of the week (again end of week = start + 6)

	Start of week
Tuesday to Monday	=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<3,-4,3)
Wednesday to Tuesday	=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<4,-3,4)
Thursday to Wednesday	=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<5,-2,5)
Friday to Thursday	=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<6,-1,6)
Saturday to Friday	=[Due Date]-WEEKDAY([Due Date])+IF(WEEKDAY([Due Date])<7,0,7)

See this picture for some test data, its in a strange order (Thursday to Wednesday if first) but check it out on your desk calendar if you’re not convinced!

Opening an URL in a new Window from a SharePoint list

Date:June 22nd, 2012 Author:Stuart Pegg Tags: Calculated Columns, Free, Guide, HTML Calculated Column, Link
Category: SharePoint HTML Calculated Column Comments:2 ;

A missing feature from SharePoint’s URL column is the ability to open links in a new window. However, our free HTML Calculated Column lets you do this in just a few easy steps.

Step 1: Get the software

Download our software from here: HTML Calculated Column Download

It’s free, but you’ll have to sign up to our newsletter. Don’t like our newsletter? Just unsubscribe. No logins, no e-mail reselling, no spam, no drama.

Just run the installer on your server and you’re done.

Step 2: Create a Text column

Add a simple Text column to your list (not a Hyperlink column). Create it as normal, call it ‘URL’ or something similar, and put an URL in it.

Calculated columns (both SharePoint’s and ours) don’t support Hyperlink columns, so it isn’t possible to use these in the formula below.

Step 3: Create an HTMLCC column

Next is our Pentalogic HTML Calculated Column (catchy name, eh?). Create it as you would a normal calculated column, and put the following formula in it:

=”<a href='”&[URL]&”‘ target=’_blank’>Link</a>”

The part that does the actual new window opening is the “target=’_blank’”, and [URL] is the name of your text column. You can customize the formula however you like; possibly with the help of our Calculated Column Cheatsheet.

When you save, you’ll see our cautionary malicious code warning. If you have users you don’t trust able to add/edit items in this list, then… well… sack them (or have them sacked). Then tick the box and click OK.