PowerShell Enable Versioning in all Site Collection Lists & Libraries

Often it’s a requirement to have Versioning turned on in a SharePoint List or Library. Versioning is a list/library level attribute and there’s unfortunately no way via the Site Settings GUI to tell SharePoint at the Site Collection or Site level to always have versioning turned on when a new list or library is created..

Traditionally, If you want versioning to be turned on in each new list/library, you’d have to either:

  • Use your own library template (with versioning turned on)
  • Use feature + event handler to programmatically activate versioning on each new list

The easiest way out of those two options is probably to use your own template. To do this, create a new document library, activate versioning, then save this list as template.

When you create a new list, you will then be able to use your template and directly create a list with versioning activated.

What about cases where we don’t want to/can’t use templates and we have admins spinning up new lists/libraries and jonesing for that versioning goodness?
The following PowerShell can be used to report on, and optionally enable Versioning on all Lists and Libraries in the site collection URL it is fed.
Minor Versioning, and Major/Minor Version quantity limit toggling is also in there but commented out. To turn on those options, simply remove the # symbol from the beginning of the lines.

PowerShell Script (save as file e.g. Enable-Versioning.ps1):

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$siteURL = $args[0]
$execute = $args[1]
$site = Get-SPSite($siteURL)
foreach($web in $site.AllWebs) {
Write-Host "Inspecting " $web.Title
foreach ($list in $web.Lists) {
Write-Host "Versioning enabled: " $list.EnableVersioning
Write-Host "MinorVersioning Enabled: " $list.EnableMinorVersions
Write-Host "EnableModeration: " $list.EnableModeration
Write-Host "Major Versions: " $list.MajorVersionLimit
Write-Host "Minor Versions: " $list.MajorWithMinorVersionsLimit
if($execute -eq "execute") {
$list.EnableVersioning = $true
#if($list.BaseType -eq "DocumentLibrary") {
#$list.EnableMinorVersions = $true
#$list.MajorVersionLimit = 3
#$list.MajorWithMinorVersionsLimit = 3
Write-Host $list.Title " is updated"

Script takes two parameters:
1. Required: Site collection URL e.g. http://mysitecollection Specifies the site collection to operate on.
2. Optional: execute Script runs in report mode only unless you add the word execute
Example 1: Run the script in report mode only (no changes effected):
    c:usersadministratordesktop.Enable-Versioning.ps1 http://mysitecollection
Example 2: Run the script in execute mode:  c:usersadministratordesktop.Enable-Versioning.ps1 http://mysitecollection execute

This script could potentially be run on an automated basis via the Windows Task Scheduler – this would let you run it on a regular basis to ensure all lists/libraries in a site collection have Versioning turned on.


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%
    Markup Gives a price from a cost and a percentage markup $120.00
    Commission Gives the commission due on a sale (based on a commission %) $25.00
    Formatting Formatted with $ curency, comma thousand seperator and 2 decimal places $1234.56
    Negative numbers in brackets (95.99)
    & Concatanate (put two text values or fields together)
    4 & “3”
    ^ Power (e.g. [Field]^2 = Squared)
    /, +, -, * Divide, Add, Subtract, Multiply
    = (Equal to) > (Greater than) >= (Greater than or equal to)
    <> Not equal to) < (Less than) <= (Less than or equal to)
    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.
    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)
    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
    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 (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
    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
    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
    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
    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
    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
    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
    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.



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


    • 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


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


    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.


  • 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
    DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(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:

    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):

Nintex – Specified value is not supported for the urlOfFile parameter

Recently after a Ninxtex migration/version update we encountered the following error when trying to publish any workflow:

Windows Event Log Error:
System.ArgumentException: urlOfFile Parameter name: Specified value is not supported for the urlOfFile parameter.
at Microsoft.SharePoint.SPFileCollection.get_Item(String urlOfFile)
at Nintex.Workflow.WorkflowRepository.NameInUse(String workflowName, Guid listId, WorkflowType& workflowType)
at Nintex.Workflow.ApplicationPages.SetName.Page_Load(Object sender, EventArgs e)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at Microsoft.SharePoint.WebControls.UnsecuredLayoutsPageBase.OnLoad(EventArgs e)
at Microsoft.SharePoint.WebControls.LayoutsPageBase.OnLoad(EventArgs e)
at Nintex.Workflow.ServerControls.NintexLayoutsBase.OnLoad(EventArgs e)
at Nintex.Workflow.ApplicationPages.SetName.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

To resolve this, go to the hidden NintexWorkflows library and check the contents of each subfolder. If your site is http://farm/sites/test the URL would be http://farm/sites/test/NintexWorkflows. If any subfolder is empty (contains no files), delete the subfolder – you should be able to publish after that.

Delete All Items from a SharePoint List – PowerShell

In experimenting with the upper limit thresholds of just how many items you can create in a list, I ran into a little problem in that the web UI went mental and would cause IE to crash (this was with 21,000 list items). Trying to access the files via Webdav was equally non-workable. I needed to get rid of these list items to even be able to roll back the crazy test.

PowerShell to the rescue. Just replace the “http://serverurl” and “ENTER LIST NAME HERE” values with your web URL and List Name.

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

# "Enter your site URL here"
$SITEURL = "http://serverurl"

$site = new-object Microsoft.SharePoint.SPSite ( $SITEURL )
$web = $site.OpenWeb()
"Web is : " + $web.Title

# Enter name of the List below
$oList = $web.Lists["ENTER LIST NAME HERE"];

"List is :" + $oList.Title + " with item count " + $oList.ItemCount

$collListItems = $oList.Items;
$count = $collListItems.Count - 1

for($intIndex = $count; $intIndex -gt -1; $intIndex--)
        "Deleting : " + $intIndex

Note that the assembly references at the top refer to SharePoint 2007 assemblies – not to worry, settings in the out-of-the-box SP 2010 Web.config will automagically bump up the references to the current SharePoint 2010 versions – there’s no burning need to go around adjusting assembly references if you come across PoweShell examples like this.

SharePoint Filter List / Library by Querystring

If you need to filter a list / library, but don’t want to have to make new View .ASPX files, it’s a simple but effective thing to know that standard Views accepts querystring filter values. So can you put in filter/order/etc. values directly into the URL to accomplish your goal. You could always batch create Views using PowerShell with a technique like the one described here, however if you want to keep things in one simple view then go with the query string approach.

http://go.mysharepoint.com/SiteName/LibraryName/Forms/ChangeLogMergeView.aspx?FilterField1=MyColumn&FilterValue1=Custom Value

You’ll need the following querystring values:

FilterField1 (first filter, column to filter by)
FilterValue1 (first filters, the string you want to filter by)

If you have multiple clauses to filter by, just add another pair of FilterField1 / FilterValue2 pairs, and increment by one, so:

http://go.mysharepoint.com/SiteName/LibraryName/Forms/ChangeLogMergeView.aspx?FilterField1=MyColumn&FilterValue1=Custom Value&FilterField2=MyOtherColumn&FilterValue2=Another Custom Value

The one to watch out for is the FilterField which is looking for the CAML Query-style name of the field. So if you wanted to filter by a field name that has spaces in it, you need to replace the spaces with _x0020_  (which represents a space character). So “Date Modified” becomes:


SP 2010 List Cell Vertical Alignment

There seems to be a trait associated with the Multi-Line Text list column type in that it floats the <p> element containing the text in the middle of the cell. Why this is, I know not. I do know that most people like their table values generally vertical-aligned to the top of the cells they reside in.

The vertical alignment not being butted up to the top is a symptom of the out-of-the-box SP 2010 Multi-Line data value class. I corrected it by applying the following CSS class to my custom branding:

.ms-listviewtable p

All your Multi-Line list column values should now appear with the <p> element wrapping them aligned to the top of their table cell.