Category: SharePoint (Page 1 of 3)

SharePoint Tips…How to colour code Calendar items


As you know SharePoint calendars are views of a list type containing columns including date and time.

You can add a choice category column of your choosing to your list and by using multiple views (overlays) use this to colour code entries in a calendar view.

The example below uses a filter called Task Status with options New, Started, Complete to be displayed with separate colours.

Category name and filter options are of course customisable.

Create Calendar

Create your calendar in the usual way

  • Browse to SharePoint site
  • Select Add an App
  • Choose Calendar, name it and click Create

Add Category Column item

  • Browse to the new calendar
  • From the menu ribbon select CALENDAR and click List Settings button
  • Click Create column
  • Name as Task Status, select type as Choice (menu to choose from), enter choices New, Started, Complete and select Drop-Down Menu or Radio buttons (you don’t want to allow multiple choices, so don’t use Checkboxes), and click OK

Create Calendar views

  • Return to the Calendar view, and from the menu ribbon select CALENDAR and click Create view button
  • Select Calendar View, name New, add a Filter option selecting Task Status and is equal to New and click OK
  • Repeat and create additional views named and filtered for Started and Complete
  • Create a new view called All Tasks, set to it Default View and use is equal to filter against Task Status but leave filter option blank. (This creates a ‘clear’ default view – this is to replace the default ‘Calendar view’ which would show duplicate values if used)

Create Calendar Overlay Views

  • From the menu ribbon select CALENDAR and click Calendar Overlays button
  • Click New Calendar

  • Add name New, select required colour from the Color drop-down
  • Click Resolve new to the Web URL field to load the site and confirm (or select) the appropriate Calendar in the List menu
  • Select New from List View, click Always Show and click OK

  • Repeat for Started and Complete then click OK to save the Calendar Overlays

The calendar default view will now display the three views overlaid showing each category as a different colour.

SharePoint Tips…How to apply colour formatting to a List row based on value in cell


Pre-SharePoint Designer 2013 if you wanted to use Conditional Formatting to colour code SharePoint List rows based on values, you could simply use Conditional Formatting in a similar way to Excel.

However, from SharePoint Designer 2013 onwards the Design View has been removed which means that using this tool as a simple WYSIWYG type editor isn’t possible.

The method described below explains how to use jquery and some short code placed in a Content Web Part to format rows based on specified text being present.

The method was provided by Arild Aarnes on SharePoint247 blog

Add code to SharePoint page

  • Go to your List SharePoint page, select Edit page and from the Media and Content category, add a new Content Editor web part – it doesn’t matter where on the page you place it as it will be invisible
  • Click anywhere in the Content editor web part and then click Edit Source on the ribbon
  • Paste in code as shown below then click OK
  • From the ribbon select Page and Stop Editing to save


<script src="" type="text/javascript"></script><script>
$Text = $("td .ms-vb2:contains('Approved')").filter(function() {
return $(this).text() == "Approved";})
$Text.parent().css("background-color", "#00FF66");
$Text = $("td .ms-vb2:contains('Approved under revision')");
$Text.parent().css("background-color", "#FFFF66");
}); </script>

This example is based on using a status column with entries including Approved and Approved under revision, though note that the text can appear in any column.

Where this text if found in the row, the formatting rule will apply so that Approved items are coloured green and Approved under revision are coloured yellow


  • filter(function)) is used for Approved to ensure an exact match avoiding matching with Approved under revision
  • Bear in mind this is case sensitive, so text must match exactly what is used in the web part code. Using a ‘Status’ column with fixed choice would be the best approach to ensure consistency
  • The first line of the code defines the path to the jquery library stored online. You’d probably want to add the jquery code to the SharePoint Site Assets library on your site and reference it there instead. Download jquery from here, copy to your sites Asset Library and replace the URL in the code with equivalent path to jquery-3.1.0.min.js on your site.

Also see


SharePoint Tips…Calculate date x days before Start Time


SharePoint Lists support a number of functions from Excel.

Below shows how to calculate a new date derived from the value in the Start Time column.


Start Time is automatically created column when you create a SharePoint calendar.

If you want to calculate a value for a second date column offset from Start Time, (or any other time column such as End Time), you need to create a calculated column.

Example 1 – calculate 30 minutes before Start Time

Create a new column ‘Reminder’ in the SharePoint list using Calculated column type and enter formula

=[Start Time]-1/48

using data type Date and Time

The calculation is based on days, so for 30 minutes use 1 day divided by 48 = 30 minutes

Title Start Time Reminder < ‘Reminder’ function >
Job 1 01/08/2015 13:00 01/08/2015 12:30 =[Start Time]-1/48
Job 2 11/08/2015 18:45 11/08/2015 18:15 =[Start Time]-1/48
Job 3 14/08/2015 00:15 13/08/2015 23:45 =[Start Time]-1/48

Example 2 – calculate 1 week before Start Time

Create a new column ‘Reminder’ in the SharePoint list using Calculated column type and enter formula

=[Start Time]-7

using data type Date and Time

Title Start Time Reminder < ‘Reminder’ function >
Job 1 01/08/2015 13:00 25/07/2015 13:00 =[Start Time]-7
Job 2 11/08/2015 18:45 04/08/2015 18:45 =[Start Time]-7
Job 3 14/08/2015 00:15 07/08/2015 00:15 =[Start Time]-7


Note: SharePoint calendar Start Time can’t be used in Workflows. If you want to trigger an action in a Workflow based on date and time you need to use the method described above to create a new reference column.


« Older posts

© 2021

Theme by Anders NorénUp ↑