Introduction

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.