February 2016 – tecivo BLOG

tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Month: February 2016

SharePoint Tips…Calculate date x days before Start Time

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.

 

SharePoint Tips…How to use calculated column in List with MID to get hostname from FQDN

Introduction

SharePoint Lists support a number of functions similar to Excel.

Below shows how to use the MID function to create a new List column containing server hostnames derived from an existing column containing fully qualified domain names (FQDN).

————

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

=MID([FQDN],1,FIND(".",[FQDN],1)-1)

using data type Single line of text

Example

FQDN hostname < ‘hostname’ function >
1 dc.tecivo.local dc =MID([FQDN],1,FIND(“.”,[FQDN],1)-1)
2 exchange.tecivo.local exchange =MID([FQDN],1,FIND(“.”,[FQDN],1)-1)
3 sharepoint.tecivo.local sharepoint =MID([FQDN],1,FIND(“.”,[FQDN],1)-1)

 

© 2019 tecivo BLOG

Theme by Anders NorenUp ↑