Tag: SharePoint

Microsoft Ignite Conference: Day 1 One Round-Up

Microsoft’s Ignite was a huge event- 23,000+ attendees -and a lot of news came out of it on day one. I’ll share with you here my take on the big items of the day, flavoured through my lens of SharePoint/Office 365.

Since there was so much going on, i’m going to break it out into day-by-day posts- easier to digest and gives me time to collect my thoughts after being hit on the head with a Chicago-sized Microsoft frying pan. 🙂

The core concepts: Microsoft is taking a mobile-first, cloud-first approach with three top priorities: to usher in an era of more personal computing, to reshape work and productivity with enhanced products and processes, and to build trust in a reliable, extensible cloud service offering.

Let’s get started. First, check out the official Microsoft news homepage for the event at http://news.microsoft.com/ignite2015/

Here’s a selection of  some of the hot sessions now available from Day 1:

Also available in the on-demand recordings is the Monday morning keynote, which was delivered effectively by Microsoft CEO Satya Nadella, and the evening keynote hosted by Harry Shum titled The Next Era of Computing: Seeing the Future Before It Happens.

spark the future
First, a little recap on the sheer scale of the event. 23,000 of some of the world’s best IT Professionals in one spot, yet the Borg-scale capacity of the McCormick + Lakeside Conference Centers seemed hardly enough:
stack overflow

The keynote began on the upswing, with a DJ pumping out music to many, many IT peeps who are perhaps used to a little more mellow entrance in Monday Morning: 🙂

A pretty unexpected kick off to the keynote: The rapper/Artist know as Common takes the stage and delivers some inspiring words:
common

Soon thereafter, the big boss Mr. Satya Nadella took the stage.

Satya Nadella

1

Announcements:

Office 2016 Public Preview
Skype for Business broadcasting
Office Delve Org Analytics
Office-Delve-organizational-analytics-screenshot
SQL Server 2016 on-premise Azure (if you want the deep-dive version, check here: http://www.brentozar.com/archive/2015/05/reading-the-sql-server-2016-data-sheet/)
Windows users can try Office 2016 and its new desktop co-editing feature
Microsoft launches Operations Management Suite to deal with infrastructure across clouds

Quotes:
“Move IT from delivering services in our org, to delivering transformation and innovation.”
“Mobile first, cloud first – but it’s not about the mobility of a single device. It’s about the mobility of the experience. It’s about the cloud back end, and adding intelligence to your experiences.”
“MobileFirst is not about a device, it’s about sharing computing across all the experiences in our life.”

“Social collaboration and co-creation are at the core of what we do.”
“There are going to be more devices than people on the planet.”

“Exchange, Skype for Business and OneDrive span both your personal and work life”
“The next version of SQL Server is considered a breakthrough.”
“It is important to us to build trust into the core of the operating system…that’s not a bolt-on, you have to build that in.”

There was a brief unscripted comedy moment when Mr. Nadella was describing a scenario where Delphi software would allow one to know exactly when their partner was coming home:
Delphi

Joe Belifiore

– Joe says about 5-8% of users use Alt-Tab to switch between apps. Adding multiple desktop support. Hold down control/Windows/right-arrow and switch between desktops, or left-arrow to go back. Dragging an app from one desktop to another got the first spontaneous audience applause.
– Demos Cortana, and Cortana driving PowerBI
– Demos Continuum, a new way of changing the Windows user interface to change between tablet mode and laptop mode depending on how you hold the device
– Demos Windows Hello, a new facial recognition authentication mechanism that works through the webcam. Takes a cloth off a webcam, and it unlocks his laptop in a matter of seconds without the user doing anything.

.. at this point I had to move on to the rest of the Conference –

The Evolution of SharePoint

– OneDrive integration improvements are a big priority for the SP team
– Experiences, Extensibility & Management are the core principles
– On track in development efforts for delivering SharePoint Server 2016 next year
– Cloud-inspired features eventually will make their way back into the server product. Functionality that doesn’t make it into the server will be offered as Office 365 services that can be leveraged by premises-based systems
– Microsoft is continuing to focus on SharePoint’s Files, Content Management, Sites and Portals components going forward. It plans to make it easier to use hybrid architectures (SharePoint Server plus Office 365 services) and make it easier for organizations to perform migrations when they are ready, according to Seth Patton, global senior director for SharePoint and OneDrive product marketing
– A new About Me next-generation portal coming to SharePoint Server 2016. It uses Microsoft’s Delve content discovery tool, based on Office Graph enterprise search technology, to surface content and organizational information. Microsoft is also planning to ship an upgrade to SharePoint Server 2013 that will enable the Delve portal for those organizations that can’t wait for the release of SharePoint Server 2016.

Information Management with Office 365 in Mind

The Next Era of Computing: Seeing the Future Before It Happens

All in all, a heavy duty day full of news, connections and learnings. Day two recap coming tomorrow, subscribe to my blog to stay looped in!

RegEx Magic – Become the Rain Man of SharePoint Programmers for 40 bucks

Regular Expressions.. the name sounds so, simple. They’re just regular expressions eh? Don’t you know?

Really, what Regular Expression are is a means to adapt stodgy, literal programming to the weird rules that humans come up with. Got a crazy inventory name that starts with 5 alphanumeric characters representing a date but ends in the Valley Girl rendition of your profit margin as matched to a syzygy? Perfect, you need a Regular Expression.

Regular expressions originated in 1956, when mathematician Stephen Cole Kleene described regular languages using his mathematical notation called regular sets.  These arose in theoretical computer science, in the subfields of automata theory (models of computation) and the description and classification of formal languages. Other early implementations of pattern matching include the SNOBOL language, which did not use regular expressions, but instead its own syntax.

A regular expression, often called a pattern, is an expression used to specify a set of strings required for a particular purpose. A simple way to specify a finite set of strings is to list its elements or members. However, there are often more concise ways to specify the desired set of strings.

Unfortunately, Regular Expressions are what make you grow bald spots and suffer chest pains. The problems they solve are so out of wack with computers ideas of what is sensible and orderly, the “regular” expressions that are needed become ridiculous endeavours in backtracking, forward looking, being optimistic, and fuzzy. What the hell..

Fortunately, in the cases where you need to be a cool developer, but not make yourself too much of a human jive-talk translator for the almighty .NET  runtime, there’s the program RegEx Magic:

RegexMagic makes creating regular expressions easier than ever. While other regex tools such as RegexBuddy merely make it easier to work with regular expressions, with RegexMagic you don’t have to deal with the regular expression syntax at all. RegexMagic generates complete regular expressions to your specifications.

First, you provide RegexMagic with some samples of the text you want your regular expression to match. RegexMagic can automatically detect what sort of pattern your text looks like. Numbers, dates, and email addresses are just a few examples of the wide range of patterns that RegexMagic supports. By marking different parts of your samples, you can create regular expressions that combine multiple patterns to match exactly what you want. RegexMagic’s patterns provide many options, so you can make your regular expression as loose or as strict as you want.

Best of all, RegexMagic supports nearly all popular regular expression flavors. Select your flavor, and RegexMagic makes sure to generate a regular expression that works with it. RegexMagic can even generate snippets in many programming languages that you can copy and paste directly into your source code to implement your regular expression.

RegexMagic doesn’t automatically generate regular expressions on magic alone. But it sure makes things a lot easier by allowing you to work with your data instead of the cryptic regex syntax.

Easily Create Regexes with RegexMagic

 

SharePoint Hire Interview Question Matrix

sharepoint-hire-interview-question-matrix-s1

What

Hiring staff in a highly specialized technical field such as SharePoint can rapidly turn into an inconsistent & time-consuming (expensive) affair, if you don’t have the right system in place. Selecting the right candidate for a SharePoint job from a pool of applicants with largely similar backgrounds & CV’s can be daunting – SharePoint encompasses, even by IT hiring standards, a huge body of knowledge and experience.

Think of all the expense & effort involved with bringing on a new SharePoint hire into your team: onboarding, setting up benefits, training, team familiarization, meetings, etc. Now imagine having to burn up all of that, if you make a less than perfect hiring decision! You stand to risk your company reputation, team trust, customer satisfaction, and so on. Most prominently, you would be right back where you started – looking for a great SharePoint expert for your team.

So What

Failure to properly screen your SharePoint hire applicants can lead to big trouble when it come’s time for them to be effective in their jobs. Once you get through all the standard HR practices & processes, the question remains: “Is this guy/gal really a SharePoint pro?”. To help determine that, an extremely effective screening method is a series of technical questions combined with a scoring system.

Now What

OPTION A- Hire itgroove’s SharePoint MVP’s to perform your interviews for you.

OPTION B- Purchase and download the SharePoint Hire Interview Question Matrix Excel Workbook for only $40. The itgroove SharePoint Hire Interview Question Matrix Excel Workbook is composed of two Worksheets:

1. Questions Scorecard
This Excel Worksheet contains 56 carefully researched, in-depth SharePoint Hiring Questions, composed from real world experience of itgroove’s 3 SharePoint MVP’s. These questions are divided into the following categories:
– General SharePoint Knowledge
– SharePoint 2010 IT Pro
– SharePoint Development
– SharePoint 2013

A Score column is used to rate your Job Applicant’s response to the questions.

All questions include a detailed answer and background explanation for the answer – you don’t necessarily need to be a SharePoint guru yourself in order to effectively grade someone with this worksheet!

2. Personal Scorecard
The Personal Scorecard Worksheet incorporates a detailed scoring formula that rates your potential hire by the following traits:
– Amount and Quality of relevant experience
– Communication Skills in Interview
– Technical Skills Level
– Enthusiasm
– Overall Fit/Suitability for Role
– Educational Qualifications
– Evidence of Research into our Company

The general interview results for these traits are entered into the Worksheet so that an overall grade for the Candidate can be achieved. This grade can then be stacked up against all your other interviewee’s results, to enable quick & fair qualification of who is most suitable for the job position.

Buy Now on the itgroove Store

SharePoint Interview Questions

CKS Dev for Visual Studio 2012 is here

The Community Kit for SharePoint: Development Tools Edition extends the Visual Studio 2012 SharePoint project system with advanced templates and tools. Using these extensions you will be able to find relevant information from your SharePoint environments without leaving Visual Studio. You will have greater productivity while developing SharePoint components and you will have greater deployment capabilities on your local SharePoint installation.

CKSLogo

This version is targeted for users running SharePoint 2010 or SharePoint 2013. You only need this version regardless of SharePoint edition or version.

Previous Visual Studio 2010 versions can be found CKS – Development Tools Edition (Foundation) and CKS – Development Tools Edition (Server)

Features

This project provides extensions to four core areas; Environment, Exploration, Content and Deployment.

Exploration extends the new SharePoint Explorer with advanced information about SharePoint sites such as the installed Web Parts and Master Pages or the Feature dependencies and elements. Also included in the Explorer are a variety of import functions to bring existing SharePoint items into your active solution.

The Content area includes advanced templates such as Linq to SharePoint, Custom Action or Delegate Control. Become extra productive while developing sandboxed solutions using the SharePoint Full Trust Proxy. Utilise the SharePoint Console Application project template to quickly build SharePoint code.

Our enhanced Deployment functions give you the ability to utilise quick deployment and almost a dozen other productivity enhancing deployment steps, including automated deployment (per file on change deployment).

Find the complete overview of all the CKS Development Tools Edition features on the documentation tab of the project site.

Print all Documents in SharePoint Library PowerShell

I put together the following script to help out someone on the internets, the requirement was to print all documents in a SP library. This PowerShell iterates through a library (folders included), downloads a local copy, then prints to the default printer on the machine it is executed on.

Swap $destination, $webURL and $listUrl parameters as needed.

Fun for the whole family and keeping our paper mill workers gainfully employed!

 

######################## Start Variables ########################
$destination = "C:"
$webUrl = "https://mysharepoint/sites/sitename/"
$listUrl = "https://mysharepoint/sites/sitename/Shared%20Documents/"
##############################################################
$web = Get-SPWeb -Identity $webUrl
$list = $web.GetList($listUrl)

function ProcessFolder {
    param($folderUrl)
    $folder = $web.GetFolder($folderUrl)
    foreach ($file in $folder.Files) {
        #Ensure destination directory
        $destinationfolder = $destination + "/" + $folder.Url 
        if (!(Test-Path -path $destinationfolder))
        {
            $dest = New-Item $destinationfolder -type directory 
        }
        #Download file
        $binary = $file.OpenBinary()
        $stream = New-Object System.IO.FileStream($destinationfolder + "/" + $file.Name), Create
        $writer = New-Object System.IO.BinaryWriter($stream)
        $writer.write($binary)
        $writer.Close()
		Start-Process -FilePath ($destinationfolder + "/" + $file.Name) -Verb Print
        }
}

#Download root files
ProcessFolder($list.RootFolder.Url)
#Download files in folders
foreach ($folder in $list.Folders) {
    ProcessFolder($folder.Url)
}

SharePoint Calculated Column Formulas & Functions – The Essential Guide


  • SharePoint uses pretty much the same engine as Excel for it’s calculated columns and field validation stuff. There are some exceptions, such as NETWORKDAYS and some other functions not being included. Try prototyping your formulas in Excel first and then applying them to SharePoint.

    The goal of this page is to become the definitive list of formulas and operations used in SharePoint Calculated Columns. I try to answer all formula requests but i’d rather see you learn the fundamentals than get a quick fix, so please read all references carefully before submitting a scenario via the comments.

    In terms of “programming is hard” – there’s not a whole lot to this stuff as it is very well defined and encapsulated. Once you master it, you will be the new Office rockstar- careers have been built on this.

    Did I miss one? Please drop a note in the comments and i’ll get it added!

    Type Explanation Output
    NUMBERS  0
    Profit Shows the percentage profit on a sale (tick “Show as percentage”) 10%
    ([Price]-[Cost])/[Cost]
    Markup Gives a price from a cost and a percentage markup $120.00
    [Cost]*(1+[Markup])
    Commission Gives the commission due on a sale (based on a commission %) $25.00
    [Sale]*[Commission]
    Formatting Formatted with $ curency, comma thousand seperator and 2 decimal places $1234.56
    TEXT([Sales],”$#,###.00″);
    Negative numbers in brackets (95.99)
    TEXT([Sales],”#,###.00;(#,###.00)”);
    OPERATORS
    & Concatanate (put two text values or fields together)
    4 & “3”
    43
    ^ Power (e.g. [Field]^2 = Squared)
    4^3
    64
    /, +, -, * Divide, Add, Subtract, Multiply
    RELATIONAL OPERATORS
    = (Equal to) > (Greater than) >= (Greater than or equal to)
    <> Not equal to) < (Less than) <= (Less than or equal to)
    DATE AND TIME
    Time only TEXT([DateTimeField],”hh:mm:ss”) 01:21:51
    Weekday TEXT([DateField],”dddd”) Wednesday
    TEXT([DateField],”ddd”) Wed
    Month TEXT([DateField],”mmmm”) October
    TEXT([DateField],”mmm”) Oct
    Year TEXT([DateField],”yyyy”) 2012
    TEXT([DateField],”yy”) 12
    Combinations TEXT([DateField],”mmmm dd, yyyy” October 17, 2012
    Fiscal Year Shows which fiscal year a date falls in (1st October)
    FY & IF(DATE(YEAR([Date]), 10, 1)>[Date], YEAR([Date]), YEAR([Date])+1) FY 2012
    Season Shows which season a date falls in. Takes into account one month offset from quarter.
    CHOOSE(INT((MOD(MONTH(When)+1,12)/4))+
    1,”Winter”,”Spring”,”Summer”,”Autumn”)
    Spring
    Quarter Shows which quarter a date falls in
    Q & INT((MONTH([Date])-1)/3)+1 Q1
    Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date]) Q1-2012
    Week Number Shows the week number (US style)
    ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+
    WEEKDAY(DATE(YEAR([Date]),1,1))-WEEKDAY([Date])+1)/7,0)+1
    5
    Week Commencing Shows the date of the first day of the week (useful for grouping by week)
    [Date]-WEEKDAY([Date])+1 3/4/2012
    Day/Night Shows whether time is day or night
    IF(AND(HOUR([Time])>6,HOUR([Time])<18),”Day”,”Night”) Day
    AM/PM Shows whether a time is AM or PM
    IF(HOUR([Time]) < 12,”AM”,”PM”) PM
    OTHER
    Modified Shows whether an item has been modified since creation
    IF([Modified] > [Created], “Changed”, “Original”) Changed
    Marks out of ten Gives general comments on a mark out of ten
    CHOOSE(INT([Marks]/3),”Bad”,”Poor”,”Good”,”Great”) Great
    Random String Chooses a string at random, based on the time (in seconds)
    CHOOSE(MOD(TEXT(Created,”s”),2)+1,”String A”,”String B”, “String C”) String C
    TEXT
    TEXT (Value, Format) Converts Value to a Text value, using Format 2012|04
    TEXT([Created], “yyyy|mm”)
    REPT (Text, Number) Repeats Text the given Number of times HelloHelloHello
    REPT(“Hello”,3)
    FIXED (Num, Dec, NoCommas) Returns Number with the given number of decimals as text (commas optional)
    FIXED(2044.23,1,TRUE) 2044.23
    FIXED(2044.23,0,FALSE) 2,044
    LEN (Text) The length of Text 4
    LEN(“Hola”)
    LEFT (Text, Number) Return X characters from the left
    LEFT(“The Quick Brown Fox”, 5) The Q
    RIGHT (Text, Number) Return X characters from the right
    RIGHT(“The Quick Brown Fox”, 5) n Fox
    MID (Text, Num1, Num2) Returns Number2 characters from the middle of Text, starting at Number1
    MID(“The Quick Brown Fox”, 4, 15) Quick Brown
    SEARCH (Text1, Text2, Num) Returns the index of Text1 within Text2,starting the search at index Number
    SEARCH(“Banana”, “Banana Banana”, 4) 8
    LOWER (Text) Text in lower case
    LOWER(“Hello”) hello
    UPPER (Text) Text in upper case
    UPPER(“Hello”) HELLO
    PROPER (Text) Capitalize first letter of each word
    PROPER(“good morning”) Good Morning
    TRIM (Text) Removes spaces from the start and end
    TRIM(” Hello “) Hello
    CLEAN (Text) Returns Text without non-printable characters added by clipboard or similar
    CLEAN(“String1? String2??”) String1 String2
    REPLACE (T1, N1, N2, T2) Replaces Number2 characters starting at Number1 from Text1 with Text2
    REPLACE(“Hello”,2,4,”i”) Hi
    CONCATENATE (T1, T2, …) Combines the string values together into one string
    CONCATENATE(“A”,” Fine “,”Morning”) A Fine Morning
    DOLLAR (Number, Decimals) Converts number to currency text, with the given number of decimals
    DOLLAR(11.267,2) $11.27
    EXACT (Text1, Text2) Checks if two text values are identical, returns boolean
    EXACT(“Hello”,”hello”) False
    MATH
    SUM (Number1, Number2, …) Returns the total of all Numbers and number-like values
    SUM(0, 2, “26”, 100, TRUE) 128
    MINA (Number1, Number2, …) Gets the smallest of the numbers, including non-number values
    MINA(0, 2, “26”, 100, “MyString”, TRUE) 0
    MIN (Number1, Number2, …) Gets the smallest of the numbers, including Text fields containing numbers
    MIN(0, 1, “26”, 100) 0
    MAXA (Number1, Number2, …) Gets the largest of the numbers, including on-number values
    MAXA(0, 2, “26”, 100, “MyString”, TRUE) 100
    COUNTA (Value1, Value2, …) Counts all values, including empty text (“”), ignoring empty columns
    COUNTA(5, 0,TRUE) 3
    COUNT (Num1, Num2, …) Averages the Numbers, ignoring non-Number values
    COUNT(5, 0,TRUE) 2
    AVERAGEA (Num1, Num2, …) Averages the Numbers, non-Number values are interpreted
    AVERAGEA(5,0, TRUE) 2
    AVERAGE (Num1, Num2, …) Averages the Numbers, ignoring non-Number values
    AVERAGE(10, 0, “”, “0”) 5
    VALUE (Text) Converts Text to a Number, Date or Time, according to its format
    VALUE(“00:05”) 00:05
    TRUNC (Number) Returns Number with decimals removed
    TRUNC(14.999999) 14
    SQRT (Number) Returns the square root
    SQRT(25) 5
    SIGN (Number) Returns -1 for negative numbers, 1 for positive, and 0 when 0
    SIGN(-5.2786) -1
    ROUNDUP (Num1, Num2) Rounds Number1 to Number2 decimals, always rounding up
    ROUNDUP(22.0001, 0) 23
    ROUNDDOWN (Num1, Num2) Rounds Number1 to Number2 decimals, always rounding down
    ROUNDDOWN(122.492, 1) 122.4
    ROUND (Number1, Number2) Rounds Number1 to Number2 decimals
    ROUND(221.298, 1) 221.6
    PI () Returns Pi to 15 decimal places
    PI() 3.14159265358979
    ODD (Number) Rounds Number up to the nearest odd number
    ODD(1.5) 3
    MOD (Number1, Number2) Returns the remainder of Number1 divided by Number2
    MOD(5, 4) 1
    EVEN (Number) Rounds Number up to the nearest even number
    EVEN(0.5) 2
    ABS (Number) Makes a number positive if it is negative
    ABS(-1) 1
    LOGICAL
    AND (Condition1, Condition2) Returns True if both conditions are True
    AND(4>=3,3>2) True
    OR (Condition1, Condition2) Returns True if either condition is True
    OR(4>=3, 3<2) True
    NOT (Condition1) Returns the opposite to the condition
    NOT(1=1) False
    CHOOSE(Num, Val1, Val2, …) Returns the value corresponding to the number. Up to 29 values can be used.
    CHOOSE(2, “A”, “B”, “C”, “D”) B
    IF(Condition, Val1, Val2) If Conditon is true, return Value1, otherwise return Value2
    IF([Modified] > [Created], “Changed”, Original) Changed
    ERROR & TYPE CHECKING
    ISTEXT (Value) Returns True if Value is Text
    ISTEXT(99) False
    ISNUMBER (Value) Returns True if Value is a Number, oherwise False
    ISNUMBER(99) { True
    ISNONTEXT (Value) Returns True if Value is not text or is empty, False otherwise
    ISNONTEXT(99) True
    ISNA (Value) Returns True if Value returns error #N/A, otherwise False
    ISERR(#N/A) True
    ISLOGICAL (Value) Returns True if Value returns a logical value (True or False), False otherwise
    ISLOGICAL(FALSE) True
    ISERR (Value) Returns True if Value returns an error (except #N/A), otherwise False
    ISERR(#REF!) True
    ISBLANK (Value) Returns True if Value is empty, otherwise False
    IF(ISBLANK([Attendee]) Needs Attendee
    FORBIDDEN COLUMNS
    Lookup columns Not supported
    [ID] Only works on column addition/update, will not work from then on
    [Today] and [Me] Only available in default columns

    AND function

    Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.

    Syntax

    AND(logical1,logical2,)

    Logical1, logical2, … are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

    Remarks

    • The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be column references that contain logical values.
    • If a column reference argument contains text, AND returns the #VALUE! error value; if it is empty, it returns FALSE.
    • If the arguments contain no logical values, AND returns the #VALUE! error value.

    Example 1

    Formula Description (Result)
    =AND(TRUE, TRUE) All arguments are TRUE (Yes)
    =AND(TRUE, FALSE) One argument is FALSE (No)
    =AND(2+2=4, 2+3=5) All arguments evaluate to TRUE (Yes)

    Example 2

    Col1 Col2 Formula Description (Result)
    50 104 =AND(1<[Col1], [Col1]<100) Because 50 is between 1 and 100 (Yes)
    50 104 =IF(AND(1<[Col2], [Col2]<100), [Col2], “The value is out of range.”) Displays the second number, if it is between 1 and 100, otherwise displays a message (The value is out of range.)
    50 104 =IF(AND(1<[Col1], [Col1]<100), [Col1], “The value is out of range.”) Displays the first number, if it is between 1 and 100, otherwise displays a message (50)

    OR function

    Description

    Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

    Syntax

    OR(logical1, [logical2], ...)

    The OR function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

    • Logical1, logical2, … Logical1 is required, subsequent logical values are optional. 1 to 255 conditions you want to test that can be either TRUE or FALSE.

    Remarks

  • Common Date & Time Formulas

    Get Week of the year =DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1

    First day of the week for a given date: =[Start Date]-WEEKDAY([Start Date])+1

    Last day of the week for a given date: =[End Date]+7-WEEKDAY([End Date])

    First day of the month for a given date: =DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))

    Last day of the month for a given year (does not handle Feb 29). Result is in date format: =DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End Date])) Day Name of the week : e.g Monday, Mon =TEXT(WEEKDAY([Start Date]), “dddd”) =TEXT(WEEKDAY([Start Date]), “ddd”)

    The name of the month for a given date – numbered for sorting – e.g. 01. January: =CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” , “08. August” , “09. September” , “10. October” , “11. November” , “12. December”)

    Get Hours difference between two Date-Time : =IF(NOT(ISBLANK([End Time])),([End Time]-[Start Time])*24,0)

    Date Difference in days – Hours – Min format : e.g 4days 5hours 10min : =YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created), DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today) < =MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today) > =DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months, “&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”

    Display SharePoint List Items Age: Create a SharePoint Calculated Column to Display a List Item as “X” Days Old
    =YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
    DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
    <=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
    >=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
    “&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”

    Workdays Logic

    Here’s a basic approach for Calculated Columns workdays logic: Working Days, Weekdays and Holidays in SharePoint Calculated Columns: http://blog.pentalogic.net/2008/11/working-days-weekdays-holidays-sharepoint-calculated-columns/

    This shows how to combine an approach like that into Workflow logic: Limiting SharePoint Workflow Due Dates to Business Days: http://dlairman.wordpress.com/2010/10/14/limiting-sharepoint-workflow-due-dates-to-business-days/”>http://dlairman.wordpress.com/2010/10/14/limiting-sharepoint-workflow-due-dates-to-business-days/</a

    Calculate work days excluding holidays in InfoPath 2010 using SharePoint 2010 and Excel Services: http://www.bizsupportonline.net/blog/2011/02/calculate-work-days-exclude-holidays-infopath-2010-sharepoint-2010-excel-services/

    A blanket warning about anything to do with time & date programming and specific ranges like work days – always keep in mind that there’s: – Your Application logic’s concept of work week – SharePoint’s regional time and date, locale & work week settings – The Users desktop/profile settings such as region, timezone, personal variations such as different work days

  • DON’T FORGET – if you really can’t seem to come up with a formula that suits your needs based on what’s available in Excel/SharePoint formulas and functions, chances are you can achieve what you need with either:

    Option A – (SharePoint 2013 only) Use JSLink to do your calculations in JavaScript on the client side. Here’s an article on the basics of implementing JSLink in SharePoint 2013. After that, you have the whole JavaScript language at your disposal to do calculations on the fly. There are plenty of JS/Jquery libraries such as this one that can handle the heavy lifting for you.

    Option B – (SharePoint 2010/2007 only) Whip up some custom XSLT as described here and make your own custom columns that play by the rules you define there.


  • If you are still stumped after reading the information on this page, feel free to drop your question in the comments section at the bottom of the page and i’ll do my best, time-permitting, to help you out. This stuff takes stubbornness more than brains so chances are, if you stick it out, you can solve the scenario on your own and learn for next time. Please give it a go on your own first before posing a question here!

    The hardest part of Calculated columns for most people is figuring out how to combine multiple functions and values together into one formula- the following article is great for explaining that part:
    http://searchengineland.com/a-foolproof-approach-to-writing-complex-excel-formulas-146641

    Make sure your formula is not capping out on one of the following limits:
    Formula length: 1024 characters
    Calculation length: 2048 characters
    Filter length: 256 characters

    You can avoid broken formulas by adhering to this basic Excel guidance (only SharePoint-related sections listed below):

Synonyms not working in SharePoint Search

The custom thesaurus functionality of SharePoint search should be old hat to those of us experienced with MS Full Text Search, which is a mature technology that’s been around for years. What may trip you up in trying to get custom synonyms working with SharePoint’s implementation is the nuances of the file paths and deployment across multiple physical servers in a farm.

A basic example of the thesaurus format is as follows. This one would match instances of “it” to “information technology”

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
 <diacritics_sensitive>1</diacritics_sensitive>
        <expansion>
            <sub>it</sub>
            <sub>information technology</sub>
        </expansion>
    </thesaurus>
</XML>

First, does your thesaurus file validate as proper XML? Sometimes a forgotten –> closing tag etc. can be a showstopper.

Thesaurus files are stored in several locations on a SharePoint Server:

  Program Files  Microsoft Office Servers  14.0  Data  Config  

Updates to the thesaurus files here will ensure that any new Search Service Applications that you create will have the updated thesaurus definitions when they spin up.

  Program Files  Microsoft Office Servers  14.0  Data  Applications  Config  

GUID is the guid of your Search Service Application, which you can get by using Get-SPServiceApplication. That powershell command will list your service applications and their corresponding GUID’s.

When you’ve verified your updated tsneu.xml files are in those locations, you’ll need to restart the search service with these commands:

net stop osearch
net start osearch

Once the service is restarted, go to your search center and plug in one of the newly added expansion/replacement terms to see your results.

If you’re running search on multiple servers, you will need to perform these steps on each server running search. If there are multiple search applications running, you have to copy your thesaurus files to each config directory under the GUID folder for each search service application.

You can run into the following situation when trying to get a custom thesaurus to kick in for the SharePoint search service:

1. You set up the thesaurus file(s), in our example we’ll use the basic tsneu.xml, which is the language-neutral default thesauraus.
2. You try putting the thesaurus XML file into

%ProgramFiles%  Microsoft Office Servers  14.0  Data  Applications  Config 

and in

%ProgramFiles%  Microsoft Office Servers  14.0  Data  office servers  applications  data  

but with no luck – you don’t see the search results.
3. You try creating a folder on each query/crawl server with the GUID of the search application.
4. Restart the services.

Still no synonyms? It may be that you have partitioned the index, in which case the path changes, and it´s not the default one but the one you set up into the Central Administration > Manage search topology.

Chang the files there, restart the search service and voila!