SharePoint Calculated Column Formulas & Functions – The Essential Guide

Written by Keith Tuomi on . Posted in Lists, Office 365, SharePoint 2010, SharePoint Online.


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

calculated columns, date, Error Checking, Forbidden Columns, formulas, Logical, Math, Numbers, Operators, Relational Operators, SharePoint, Text, time

Comments (137)

  • shakil

    Reply

    I am using a sharepoint list and trying to set a single line of text column to be numbers only and no more or less than 6 characters in length.

    • Keith Tuomi

      Reply

      You would need to use the List Validation Settings and compose the formula there.
      Column validation options allow you to define additional limits and constraints for your data. For example, you may want to ensure that a value in one Date column occurs after another Date column. (So for example, column validation can ensure that the date in the Date Finished column can’t be earlier than the date in the Date Started column — you can’t finish a project before it’s begun!)

      To use column validation on your app:
      1.In your app where you want to validate data entry, click the List Settings button on the List tab of the Ribbon.
      2.Under General Settings, click the Validation Settings link.
      3.Type a formula in the Formula text box. The result of the formula must evaluate to TRUE to pass validation. The formula syntax is the same as calculated columns, which is similar to Excel syntax.
      4.Enter a user message that you want to appear if the validation formula fails.The message should give the user an idea of how the formula works and how to fix the problem.
      5.Click the Save button.

      In your case, you’d probably be best off to (if possible) create a new column of the type Number. That would already deal with your requirement to validate that it’s a number, and is in general best practice – if it’s a number, you should most likely have it as a number column. Then, go in an add the following validation rule to ensure that it is always only exactly 6 characters in length:
      =LEN(MyColumn)=6

  • Shakil

    Reply

    Hi Keith

    Really appreaciate your swift response, thanks

    However, I have implemented your solution and it works fine but displayys a coma which i dont require.

    I have found another formula to remeove comas

    =ISNUMBER(mycolumn+0)

    Is there a way to combine te two?

    Kind regards

    Shakil

    • Keith Tuomi

      Reply

      Aha so that’s why you were looking at using a Text field type originally. Here’s some options in order of least difficulty to highest:

      Option 1 – Create a new calculated column based on previously validated column
      Create a new column of the type Calculated Column (calculation based on other columns), and choose Number for the sub-type. In it’s calculated value, put the following formula (being sure to substitute your previous column name where your data sits): =TEXT([MyColumn],”0″)

      Doing the =TEXT is similar to the (actually quite clever) way you had proposed of adding a +0 to the formula.

      Option 2 – Modify output via XSLT
      Format the output of the original field (the way it’s rendered from the end users standpoint) by modifying it with XSLT.

      Replace the standard ‘ListView’ webpart with ‘DataForm’ webpart. After you did it, you could use the ‘format-number()’ function to format the number with SharePoint Designer.
      e.g.

      <td class="ms-vb">
  <xsl:value-of select='format-number(UnitPrice,"#.00") '/>
</td>

  • dbuchanan

    Reply

    Your site is all but impossilbe to read because the text is midnight gray over a black background. The only possible way to view it to do a “Select-All” thus making the slected text show reverse. I am using IE8.

    • Keith Tuomi

      Reply

      Thanks for the tip however it’s looking pretty normal from various desktops and browsers I’ve tried, including my IE8 emulator. If you try clearing your cache (press Ctrl-F5) does that help? I’m sure it’s some combo of WordPress and caching that’s making life difficult for us here.

  • Kacey

    Reply

    This article has been immensely helpful for creating validation and calculated column formulas, but now I’ve run into one that I can’t get. This may be impossible, but I feel like it should be:

    I have a Sharepoint list, with a text columne “Release Date” and a date column “Start Date”. I want to check if “Release Date” is not blank, then use it to populate “Start Date”.
    I put this into the “Calculated Column” box for ‘Start Date”, but it errors out:

    =IF(NOT(ISBLANK([Release Date])), [Release Date], [Today])

    Let me know if you have any ideas.
    Thanks!

      • Kacey

        Reply

        So yea, I created a [Today] column as described in that post, but still no go; in fact, I can’t seem to get any ISBLANK formula working, no matter what columns or strings I use.
        I’ve shortened it to:
        =IF(ISBLANK([Release Date]), [Today], [Release Date])

        Nothing. I’ve also tried:
        =IF(ISBLANK([Release Date]),””,[Release Date])
        =IF(ISBLANK([Release Date), [Start Date],[Release Date])
        =IF(ISBLANK([Release Date]), TEXT([Start Date]),[Release Date])

        All with the return data type as “Single line of text”, none will go through.

      • Ron

        Reply

        I think I stumbled across an obscure fix to the problem. I’m not the most tech savvy SharePoint guy in the world which is why I can’t believe someone else hasn’t already figured this out, so apologies if it’s old news. I found that adding parentheses following the [Today] function allows it to be used in calculated columns! [Today] doesn’t work, but [Today()] does. There’s no need to create a separate SharePoint column called ‘Today’ as many have advised. If I’m wrong, please do let me know, but this seems to be working very well for me.

        Example: calculated column “NDL” (Net Days Left) comparing Today() to the planned finish date, “Target” in my case:

        =(DATEDIF(TODAY(),Target,”d”))-INT(DATEDIF(TODAY(),Target,”d”)/7)*2-IF((WEEKDAY(Target)-WEEKDAY(TODAY()))<0,2,0)+1

        If you can verify that this works, please spread the word for this very irritating/nagging problem.

        • Kevin.Classen@Live.com

          Reply

          This has never worked in SP2003, 2007, 2010, but in the SP2013 environment I’m working in I’m able to calculate record age using a formula of =Today()-[Modified]. I’ll clean that up a bit for my purpose, but it gives me a valid number that I can work with.

          Thanks for pointing out that the dreaded date math from today problem appears to be resolved. I’ve not seen mention of this anywhere else, and I’ve been looking. Thanks again.

  • Martyn Cook

    Reply

    Hi Keith, I’ve been asked to pre-populate a date field (Payment due) with the 20th day of the current month. Is this possible and if so how?

    cheers
    Martyn

  • prasad

    Reply

    Hi Keith, i have requirement that to add auto-populated column in this format ” date_time_sequence no”. Here date will be selected by user in the form, time will be time of submitting and sequence no should be a no in 6 digits(ex:000001,000002) it should be incremented for every new request.
    Please help out to do this…
    Thanks & Regards,
    Prasad Kommuru

  • Brian Hadley

    Reply

    Keith,

    I am looking to have multiple formulas in one column. Do I use an if function for this and associated it with the factor that differentiates the two formulas?

    Thanks
    Brian

  • rutvik

    Reply

    i want only text from user, if user enter space or number then i want to show him error in share point 2013. how can i do this?
    give me some help

    • Keith Tuomi

      Reply

      You can create a custom field type for your requirement as shown in the Walkthrough on MSDN

      You’d want to wire up a regular expression like [a-zA-Z0-9×0020]

      If you don’t want to use custom field type than I suggest to convert your list forms on InfoPath forms and in InfoPath you can use Rules to make sure the input is required pattern. The video shows how to validate a textbox input for particular pattern.

      PS: InfoPath forms are only available in Enterprise version of SharePoint

    • Keith Tuomi

      Reply

      Hi Rutvik,

      For most lists (some like Event lists restrict this functionality) you click “Customize Form” in the ribbon. That creates 3 new pages (new, edit, display) and a new InfoPath form template with custom views for each page. The pages have an InfoPath Form Web Part for displaying the form in its relevant view (Display page shows the form in read-only mode with no buttons, etc). The initial “design” is just generic like a list form, but it’s all ready for you to design and add logic (secondary data connections, views, buttons, conditional formatting, etc). The form is directly tied to the list and is NOT a separate entity like a form library.

      Check this post for getting start with Regular Expressions in InfoPath: http://jtonsp.blogspot.ca/2012/08/infopath-regular-expressions-regex.html

  • Christy

    Reply

    Hello Keith. Is there a way to get a total of an entire calculated column in list on SharePoint 2013? then show this in it’s own webpart?
    I have a list that has a status column. I needed to find out how many of the items were in different stages of the process, so I created two calculated columns (one called “Not Started” and the other “Editing”). Now I need to know what the total is per column to compare them together. How can I do this? Thanks for your help!!

  • Tom Phillips

    Reply

    Hi, Keith,
    This may be out of your experience but can you suggest a way to prevent duplicate dates in a list? Example: I have a truck that can be loaned out to 10 people at different times during the month. If person A schedules the truck from, say, Oct. 10 thru Oct. 15 2013, and Person B enters his schedule for Oct. 12 to Oct 13. That means the truck has a duplicate schedule. I need to notify Person B the truck is unavailable then. Any pointers would be most appreciated. By the way, I can get a SP workflow to do this, but it only works if the start or end times are in the current day, not in another day.

    • Keith Tuomi

      Reply

      Hey Tom,

      Why not use Exchange for scheduling? You could set up trucks just like you would set up a conference room. Users would attempt to book the truck just like the procedure for a room, in Outlook. If there was a scheduling conflict it would warn them proactively.

      Other than that, if you’re strictly working from the SharePoint realm- it could be done with a custom InfoPath form on the data input side of your list, or even a custom feature like an event receiver but the main problem would be that people wouldn’t get to see that the truck is booked until after the data is submitted. A more practical solution might be to use a SharePoint Calendar list with the standard calendar view on the front end. Users could visually see that a resource (truck) is booked. It wouldn’t actually prevent them from cross-booking a truck, but you could simply create a corporate rule that anyone who books a truck over someone else will be shipped out on the next truck. 🙂

  • Manali Lande

    Reply

    Hi Keith,
    I have created a list with start and due dates as the two default columns in it. I have added another column with type as calculated value and I enter the formula
    =IFDATED([Start Date],[Due Date],”d”)

    I expect to get the number od days difference among the two.

    But once I save I get an error –

    Error
    The “ListViewWebPart” Web Part appears to be causing a problem. Specified argument was out of the range of valid values.

    Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: f1f1f71f-72c6-4fe5-8e96-e3123b5c8bc7

    Date and Time: 11/21/2013 3:39:31 PM

    I cannot understand what I am doing wrong. Please help

  • Adam Reyes

    Reply

    Keith I’m having an issue where I’m trying to make sure that a Name column is being typed in using proper case instead of upper or lower case. I’m using the following formula

    =PROPER([First Name])

    The problem is that even though I put in a Proper First Name is still errors out and will not submit the form. I’ve tried different variations of upper and lower than switching back to Proper but nothing seems to remedy the issue. I’ve even tried different variations of the formula to no avail:

    PROPER(“”)
    =PROPER(First Name)
    =PROPER(“David”)

    I’m not sure what I’m doing wrong, please help!

    • Keith Tuomi

      Reply

      Hey Adam, that’s a weird one as =PROPER([First Name]) is the right syntax. There must be something else in play- i’d suggest recreating your form from scratch to ensure something else hasn’t got gummed up.

  • Richard

    Reply

    Hello Keith!

    I’m new to SharePoint 2013 and have a dilemma that I can’t seem to find a solution for at all. I have implemented SP 2013 as our Intranet and one part that I still can’t get working is the room booking. We have many rooms and what we would like to do is use overlays to show the availability and allow for bookings. I started by creating a calendar in SP and adding a column for “Contact Person” so that there is a contact person for the room booking. I then used this as a template and created a few more with the names of our room bookings. I overlayed the calendars and all is good. The issue is the Check Double Booking. Any suggestions? I want it to display a message indicating to the user that they can’t book the room for that given time. When I try to use the Check Double Booking validation on the calendars I get the message displayed all the time and it won’t let me book, even though the calendar is empty. I’d prefer to strictly use out of the box solutions as I have been out of the coding game for quite some time.

    Thanks,

    Richard.

  • Tracie

    Reply

    Question…. I’ve been looking for days now on adding COLOR to a column and haven’t found anything that works that doesn’t consist of changing templates, etc., at an administrative level. I just moved from SharePoint 2010 to SP 2013 and with the changes in Designer my old way doesn’t work. I currently am using the formula

    =”-”

    however my output show as the truncated formula. I realize somewhere I need to add… disable-output-escaping=”yes”

    I used to add this in SharePoint Designer 2010 but no longer have the functionality in SP Designer 2013.

    Would you be able to help me out?

  • harsh

    Reply

    Can you please help me with formula

    I want to keep one of the sharepoint column between 6 to 8 numbers.

    Can you help me with length function
    I am trying
    =AND(LEN([Client File No]))>=6,LEN([Client File No]))<=8

    but doesnt work
    Harsh

  • Laura

    Reply

    Hi, great content but to be honest I am still starting to work with formulas and it’s not so easy to find simple examples :-).

    What I need are three things:

    Column validation – I want a fix title – no one should be able to change this title. I tried with a simple formula like =”Time Management – PS” but now I received the error message each time I click on the item. What is wrong?

    Second – add maximum of 10 numbers in a field. What we mean is: a fix number of characters! From a minimum; zero to ten? I can only find very complicated formulas. 

    Last but not least where can I find something easy explained to create cascading drop down BUT within one list? Or is it not possible?

    We are only working with Sharepoint Foundation 2010!

    Maybe some hint where I can find some information that are more for dummies :-), that would be great.
    Thank you
    Bye
    Laura

  • Aparna

    Reply

    Hi,
    When I use export to excel for my library, I am not able to export version field. Tried using workflow but it shows 1 version less than the original (eg.if original version=0.2 the custom field versions=0.1). Please help me out.

    • Keith Tuomi

      Reply

      Not sure offhand but I do know versioning on Lists is different than versioning in Libraries – Library documents have an additional FileVersion attribute in addition to the item version that is present on List items. You would have, for example, item version 1.2, 1.3, 1.4 and then 1.5 (the highest version) would be the File Version. Programmatically this means certain scenarios will seem to be “off” by one. I’m not sure if this is related to your scenario.

      You could look at this utility and see if it exports the data you need: https://exportversionhistorysp2013.codeplex.com/

  • Roberto

    Reply

    Hi,

    I’m new to column validation and I’m having a really hard time specifically finding numeric column validation formula’s. Well at least the one I’m looking for. 🙂

    I need to restrict the amount of numbers in a numeric column (called “Employee ID”). Employee ID number should not exceed the max 10 characters limit.

    Is there a way to do this?

  • yamuna

    Reply

    Hi Keith,

    i want to extract the middle text from sentense.
    For ex: “A new document created(12345) for your review” in this phrase i want 12345.

    Please help me

  • Arzan

    Reply

    Hi Keith
    Great article. This may or may not be related to calculated column, but you seem to have a great deal of knowledge around sharepoint so would like your input.
    I’m trying to calculate a “Total time spent on case” Which essentialy will track when a user creates an item to when the submit or save it, then add in every subsequent edit and how long that took.
    So if someone in a complaitns department took a complaint today, spoke to the customer for 1 hour and then another hour over 5 days, it should give me a total of 5 hours.

    That possible?

    • Keith Tuomi

      Reply

      Hi Arzan,

      For that scenario you would likely want to use a forms solution such as InfoPath (for the data entry part) and/or a SharePoint Workflow. You first need to have an interface that operators can record how much time they spent dealing with the customer, via the form input. When they click “submit”, you could use InfoPath rules or Workflow logic to perform any calculations necessary on the times recorded before it get’s stored in a SharePoint list. Adding Workflow to the mix would give you procedural logic, for example, if the Complaints Department case was marked “Serious”, the Workflow could forward an email to a Manager automatically.

      Here’s a short tutorial vid on getting started with creating a InfoPath 2013 form: http://www.youtube.com/watch?v=YykPToPH0Cc

      Although it’s kind of dated now as it uses SharePoint 2007, the example of a basic Help Desk system with Workflow can found at http://www.mssharepointtips.com/tip.asp?id=941. Pretty much everything is the same in that example for SharePoint 2010, SharePoint 2013 & Office 365 SharePoint online – just the look and feel of the UI is updated.

  • Kane

    Reply

    Hi Keith,

    Was wondering if there is a way to filter time fields so they only count business hours (9-5 Mon-Fri). I want to be able to track how long a task has been listed counting business hours only. I have tried a few things but all to no avail thus far, any help or advice would be much appreciated.

    Regards,

    kane

  • Pete

    Reply

    Hello,

    I am attempting to show the difference between two fields both of which calculate a date and time value. Is there a way to show the difference in time (days, hours, and minutes) between two fields?

    Cheers

    • Keith Tuomi

      Reply

      Sure, it’s kind of barbaric looking but here you go:

      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) =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"

  • AL

    Reply

    Keith, my formula for determining a future date is not working. In a list on column 2, I have a date. On another column, I want to generate an automatic future date, 20 days out, based on date in column 2. This is what I tried to use: =DATE(YEAR([Column2])+0,MONTH([Column2])+0,DAY([Column2])+20)

  • Jennifer

    Reply

    Hi Keith, we are doing a db attach upgrade from SP 2007 to 2010. I have one Task list that has a calculated field and the output is a hyperlink. The formula is intact in SP2013 after the db attach but the output is not clickable anymore. Any ideas would be much appreciated. Thanks!

    • Keith Tuomi

      Reply

      Hi Jennifer,

      On Sharepoint 2007, a calculated field could “become” a hyperlink without effort; you’d put in a http://etc, and it would create a hyperlink for you. When migrating to Sharepoint 2010, that “feature” is gone; the hyperlinks on calculated fields don’t exist automatically. Sharepoint 2010 supplies a hyperlink column that does this behavior for you.

      Since you probably still need to use the Calculated Column – here’s some options for you in order of complexity:
      1. Free solution the allows HTML in Calculated Columns:
      http://www.pentalogic.net/sharepoint-products/free-stuff/html-calculated-column

      2. Sneaky Trick:
      http://devdotnotes.wordpress.com/2012/01/29/sharepoint-caculated-column-and-hyperlink-no-workflow-or-script-needed/
      3. Custom JavaScript
      4. Custom XSLT

      • Jennifer Richter

        Reply

        Hi Keith, thanks for the reply. This however did not help me. I meant to say that we did a db attach and went from 2007 to 2010 to 2013. The calculated column rendered normally with the hyperlink prior to running the 2013 visual upgrade.. switching to a numbered type didn’t do anything.. and I don’t need highlighting. As you said the formula is built with the http://etc. Content editor webpart just displays the formula so that didn’t work either. What a pain..we have this task list with 100s of items and now none of the links in the calculated fields work, they just look like a long formula being displayed.

  • Jorge salsamendi

    Reply

    Estoy haciendo una formula donde los valores a evaluar vienen de un dropdown,el problema es que los valores que vienen en dropdown no me lo toma en la formula.
    Si tienes idea porque te agradezco..

    • Keith Tuomi

      Reply

      Hola Jorge,

      The translation comes out as:
      “I’m doing a formula to evaluate where values ​​come from a dropdown, the problem is that the values ​​in dropdown are not taking me in the formula.
      If you have idea why thank you ..”

      I’m taking from this that you’re trying to do a calculation based on a Choice field type (dropdown). What kind of choice column do you have? If it’s multiple values- then its not allowed to include it in a Calculated Column/Validation formula.

      If there’s only one option then you can use it like so: =IF([Column A]=”Important”,[Column B]+[Column C],”Not Applicable”)

      If there’s more than one option, you could create another new single text field and populate it with your choice field values – either using SharePoint Designer workflow or using JQuery/Javascript on the page.

      Then, you could use SEARCH(“2013”,[newField]) to see if it has 2013, and maybe mix that up with checking the length of the field. If it has the string 2013 but it’s longer than 4 (or maybe 6, there might be a ;# you have to deal with), then it would be a no. Something like
      =IF(AND(SEARCH(“2013″,[textField]), LEN[textField] = 4),”No”,”Yes”)
      that means:
      If it has a 2013 and the length is = 4, 2013 is the only values and it should be ‘No’.
      You’d have to test if a single 2013 value is 4 in length or 6.

  • Mayank

    Reply

    Hi Keith,

    I have set formula to validate the items in my SharePoint list. I am using OR to validate the item with some text, when i add more than 30 arguments, it fails with syntax error. however, it works fine with 30 arguments. I see at that you mentioned at the top that OR allows 1 to 255 arguments, am i missing anything?

    Thanks

    • Keith Tuomi

      Reply

      Hi Mayank,

      How long is your formula? Perhaps you are hitting one of the following limits:
      – formula length: 1024 characters
      – calculation length: 2048 characters
      – filter length: 256 characters

  • Katri Saarnela-Martin

    Reply

    Hi!

    is there a formula for Sharepoint 2010 calculated column which will return the count of words separated by commas?
    I can do this in excel with formula: =LEN(A1&”,”)-LEN(SUBSTITUTE(A1;”,”;””)), where A1 -field has data: 1,2,3,4, and the formula returns be the count of 4.

  • Harsh

    Reply

    Hello,
    I have custom list with two column (Follow up required by supervisor & Resolved By)
    Column 1) Follow up required by supervisor (Dropdown menu choices)
    Not Resolved
    Resolved
    Column 2) Resolved By

    if 1st column Follow up required by supervisor = Resolved
    then
    I want to automatically fill 2nd column – Resolved by =>current user name
    (who has selected Resolved)

    Is it possible Keith and can you let me know what formula should i write

    email address is – smartboy_harsh@yahoo.co.in

    Harsh

  • kisha

    Reply

    I need your help. I need a fomula to calculate date and time.

    First issue:

    cant be more than 15 days from the start date (not the created date)
    Must be 4 hours per day only

    Item 1:
    5 days + 4 hours per day should always be true
    5 days + 1 hour per day should always be true
    5 days + 5 hours per day should always be FALSE
    5 days + = 4 hours should always be false

    More than 15 days + more than 4 hours per day should always be false
    15 days or less + 4 hours or less should always be true

    Your help is really appreicated!

  • Keith

    Reply

    Could you please make the font on this article a little lighter in color? Pale silver on white is way too easy to read and I like a challenge. Maybe rgb(253,253,253) over rgb(255,255,255)? Thanks!

  • Ana

    Reply

    hi keith

    i have a feedback form list which contains the score given by employees for any event took place in the organisation. n number of users can give feedback against any event. so i want to calculate the average score of each event given by employees. can u help me to solve this issue.
    Thanks in advance.
    Regards
    Ana

    • Keith Tuomi

      Reply

      Hi Ana,

      Approach 1 – Create a view, make sure the column to be averaged is included. At the bottom of the view definition, in the Totals section, select the column, and select the Average option. The view will display the average, but the caveat is that it will only display the average of the items displayed on the page. So if you have 1000 items in your list, and the view only displays 100 items at a time, the average will be for those 100 items.

      Approach 2 – Use one of the variants of the Average functions available in SharePoint calculated columns:
      AVEDEV Returns the average of the absolute deviations of data points from their mean
      AVERAGE Returns the average of its arguments
      AVERAGEA Returns the average of its arguments, including numbers, text, and logical values
      AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
      AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria.

      • Ana

        Reply

        Hi Keith,

        In the feedback form list i have score for differnt events. How to differentiate them and calculate score for each event.
        Thanks in advance
        Regards
        Ana.

  • Diann

    Reply

    Hi, I have created a Order Number fields on a list with following formula =AND(LEN(SAPOrderNumber_OSS)=10,ISERROR(FIND(” “,SAPOrderNumber_OSS))). It works fine when I am in data sheet view but created a basic sharepoint form out of sharepoint designer. When I attempt to enter an accurate 10 digit number on the form I receive an error message advising it is no 10 digits or has a space. Since I created the form out of sharepoint designer do I have to validate there as well?

  • Michelle

    Reply

    Hi Keith,

    I’m trying to get “High” or “Low” to show when a calculated column is within a certain range, but keep getting a syntax error.

    I’ve tried…IF([Value]>3,“Low”, IF([Value]3, “Low”, “High”)

    NB. [Value] is a MAX value column.

    Its probably something simple, but I just cant get the syntax right.

    Michelle

  • Cecilia

    Reply

    Hi: I have a date column and I am trying to create a calculated value to represent that date in quarterly format. Not sure the syntax to use… Do I need to replace [Date] with the name of column that has the date ?
    Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date])
    Can someone provide the complete formula I need to enter ? many thanks

  • Kathy Dreiling

    Reply

    Hello Keith: I am writing today to see if you can help me determine if I am attempting the impossible. I have a custom list that I want to count the “Yes”/”No” results of a series (12) of question columns. So I created a number column and I typed the following formula. I get a result of 12, but can’t seem to get it to only count the “Yes” results and bypass the “NO”. I will also create another column that will count the No’s. I hope you can provide some insight as to why this is failing. Here is the formula.
    =COUNT(IF([Adequate Provider Plan?]=”Yes”,1),
    IF([Clinical Process?]=”Yes”,1),
    IF([Cultural/Linguistic Barriers?]=”Yes”,1),
    IF([Current Diagnosis?]=”Yes”,1),
    IF([Discharge Planning?]=”Yes”,1),
    IF([Labeled Accurately?]=”Yes”,1),
    IF([Medical Issues?]=”Yes”,1),
    IF([Medication History Review?]=”Yes”,1),
    IF([Past Treatment?]=”Yes”,1),
    IF([Properly Documented?]=”Yes”,1),
    IF([Smart Goal?]=”Yes”,1),
    IF([Why Now?]=”Yes”,1))

  • David

    Reply

    Hi Keith,
    Excellent source of information!
    I need help to determine the formula for the following scenario:
    1) Column 1 is a Yes/No column
    If unchecked then no date should be calculated (leave it blank).
    If checked, then return the event date in short date format (mm/dd/yyyy). This date should remain constant unless:
    2) If the box is subsequently unchecked, then make the date blank
    3) If the box is later checked again, then insert the date of that event
    Thanks in advance!

  • Christopher Mccabe

    Reply

    Hi Keith,

    I am trying to have a Due Date field populated automatically that excludes weekends and ideally holidays. The Due Date is also dependent on a Date Received field which is dependent on a Time Received field. Its complicated, but I have it working in Access, I am just not that familiar with sharepoint. Please email me and I will share the code and go into more details if necessary. Thanks.

  • Jonny

    Reply

    Thanks for the very useful article.
    This seems to give the correct result for the last day of the current month:
    =DATE(YEAR([Today]),MONTH([Today])+1,1)-1
    It subtracts one day from the first day of the next month. I’ve not tested it a great deal, but it may provide the basis for a simpler solution to the “Last day of the month for a given year” formula.

  • Cleveland

    Reply

    Good morning. I am creating columns in a project calendar field. I have a set amount of days lets say 100 to perform certain tasks. the issue is I need to count down to the last day but my days slide. In other words all my days may not be consecutive. As a result my calculations are off due to the skipping possibly of numerous days.

    I still have to account for the days on a calendar view for the billing of my time but I cant seem to set up the calculations in a sharepoint 2013 view.

  • Christy

    Reply

    I am working with a SharePoint list and InfoPath form. I want to create an auto assign functionality off of another field, but not sure what is the best approach. I have a drop down field called Functionality and a person/group field called Assigned To. I would like the assigned to field to auto populate based on what functionality is selected by the user, but when the assigned to opens the open they need the ability to change the assigned to field. What is the best way to accomplish this? thanks!

  • John

    Reply

    Hey Keith, I love this page. Thanks!
    I attempted every avenue before contacting you.

    Is there any way to check if a [Field] = “High” Set value to 1, if Field = “Medium” Set value to 2, If [Field] = “Low” Set value to 3 and if [Field] = “NA” Set Value to 4.

    Pulling my hair out here, any help apprciated

    • Keith Tuomi

      Reply

      It’s tricky with the nested statements, because one’s brain has to deal with splitting apart the brackets and placing the closing brackets way on the end. Here’s what your formula should look like:
      =IF([Field]=”High”,”1″,IF([Field]=”Medium”,”2″,IF([Field]=”Low”,”3″,IF([Field]=”N/A”,”4″))))

  • Ken

    Reply

    Hello Keith, I’m using a simple formula in a calculated field to produce the month from a different date field. The formula I used is TEXT([DOB],”MMMM”), but I get “December” if the source field value is blank. Any advise?

  • Jennie

    Reply

    Hi Keith!
    I have a problem that I can’t solve… I have an issue list with a choice column named Status and a date/time column named Date closed. I need to verify that if Status = Closed and if Date closed = empty, then Date closed will get todays date. Is it possible without coding or using Designer or so, I’m working with SP2013 (O365).
    Thanks for advise!
    Jennie

  • Joshua

    Reply

    Hi, I am trying to create a calculated column that will look for text in a column and if it is equal to a value in that column, it will return a date based on a date in another column. Here’s what I came up with and I keep getting an error.

    =IF([Rec Award]=”MSM”,[PPD]-90,IF([Rec Award]=”LM”,[PPD]-120,[PPD]-60))

    Column [Rec Award] has 12 values, but I’m only concerned with two of them. I seem to remember making this work before, but I cannot remember how I did it.

    Thanks
    Josh

  • Mike

    Reply

    Keith, I am working with MS Project that allows you to add and subtract days and it takes the set number of days for each task and adds or subtracts those days. I am trying to create a calculated column that can do the same. I have 13 milestones (number of days) that have to be able to move the schedule dates backward and forward. Doe this make any sense?

  • Mike

    Reply

    Keith, Is there a way to create a calculated column that will add and subtract a set number of days based on the dates changing before or after the calculated column?

  • Amelia Whitlow

    Reply

    I am not very familiar with formulas and I cant seem to get this column to work
    I need the column TITLE to have a formula like this, where it pulls the info from these fields and displays it. But i cant get it to work.

    =[Leave Description] [MAPI Contact]

    • Keith Tuomi

      Reply

      You can concatenate strings and numbers in a variety of ways:
      =CONCATENATE(Leave Description,” “,MAPI Contact)
      =(Leave Description & ” ” & MAPI Contact)
      =(Leave Description + ” ” + MAPI Contact)

  • Mike

    Reply

    Keith I have a formula that adds any given number of days to a date and excludes business days to give you a due date. I added 13 of these together for a schedule and the formula is placed in a calculated column and looks at the preeceedsng calculated column date which is a date time column to give it another start date. I would like to run the formula in reverse which would require the formula to look for a blank field in the start date and if blank use the field behind it. I’m struggling here and was wondering if you could help?

  • Jennie

    Reply

    Hi Keith!
    I have a problem that I can’t solve: I have an Issue tracking list that has a column named Issue Status and a column named Date Closed. I would like to create a formula that sets todays date in Date Closed column if Issue Status is set to Closed. Is it possible to do that without advanced coding? I can only work in the GUI, Designer is disabled.
    Thanks for advise!
    Jenne

  • Brian

    Reply

    Hi Keith – I’ve read through your article and all of the comments, but didn’t see our scenario addressed.

    Is it possible to check whether a field CONTAINS (or includes) certain text?

    We’re able to use

    =[field] = “some text”

    but that checks for an equality. We want to check whether “some text” is included in the field, if possible.

  • Daniel

    Reply

    Hi

    A unique post which is a definite bookmark!

    want to show a date field in uk “mm/YYYY” format. Now I can to this a an additonal calc text field say based on [Created] .
    However, say I have field [Renewal] as date field. If edit the Site column then in the “Calculated Value” , I add =TEXT([Renewal], “mm/YYYYY”) then I note this causes an error since it is note a date field. Hence, how would I create my date field with my desired format without having to create 2 fields: Renewal (Date) and RenewalFormatted (Calc)

  • Nancy

    Reply

    I have 4 columns in a list (all numbers from 0 – 3). I need a calculated column that averages these 4 values, but only includes them in the calculation if the value is greater than zero. Is this possible?

  • Rebecca

    Reply

    i am trying to perform a calculation on a choice field. choice field has roughly 10 items to choose from. i want to add another field to count how many a person chooses using a formula like: =counta([fieldname]). Unfortunately, i am not able to choose the field name b/c possibly the field type being a choice. I do not have Info Path or Designer access. I am owner of the site. Is there any work arounds for this?

  • Raghu

    Reply

    I want to create a common calender for 3 trainers and if there is booking for specific time then no one can book training for that time slot. I want to do this with list validation, calculated column in sharepoint 2013.
    Thanks.

  • Harsh

    Reply

    Hi Keith,

    I have list consisting column name “Date of Birth”

    i want to calculate and filter data whose age is >=65 years

    Can you help me with this?

  • Streamline

    Reply

    Hi Keith,

    I am looking to do a 1 hour restriction on booking/creating an event in the calendar. I currently have this validation formula running in the calendar and want to add this too it to have better control over the calendar.

    =AND(HOUR([Start Time])>=8,HOUR([Start Time])<12,WEEKDAY([Start Time])1,WEEKDAY([Start Time])7)

    Where would I be able to add the 1 hour restriction or if I could change up my current formula to better fit that addition that would be awesome.

    Thanks

  • Nick

    Reply

    Hi Keith,

    Could you help me with adding a 1 hour restriction to a calendar event?

    For example it would just be like:

    Start Time = 8:00 am
    End Time = 10:00 am

    ERROR

    Start Time = 8:00 am
    End Time = 9:00 am

    CORRECT

    I want it to throw an error if the start and end time are combined greater than 1 hour.

  • Kristian

    Reply

    Hi Keith,
    Thank you for a great site.
    I was hoping i could get your help with a calculated field in Sharepoint.

    I have 4 choice columns. Each of these columns has the following options: Displayed or Not Displayed.

    I’m looking to add a calculated field that will look at these 4 columns and if they all show “Displayed” to mark it as “Correct.” If any of the columns is showing a “Not Displayed,” I want it marked as “Incorrect.”

    I’ve used the following and it gives me “Incorrect” in all responses:

    =OR([COLUMN 1]&[COLUMN 2]&[Column3]&[Column 4]=”Displayed”,”Correct”,”Incorrect”)

    Thanks for any help that you can provide.

  • Adrian Mandile

    Reply

    Hi Keith,

    Great and informative list. Have been working a bit with calculated columns with number type format to return HTML text and display clickable URLs. Is there any hope that the Excel HYPERLINK(link location, [friendly name]) function would be made available in SharePoint? And for it to render as it does in Excel – as a clickable hyperlink instead of HTML text in forms (View Items)? PS: to left justify the number formatted URLs, use <a style="float:left"… ahead of the hrefs.

    • Keith Tuomi

      Reply

      While we can’t foresee what Microsoft might change, you can potentially influence choices and propose an idea like making the Excel Hyperlink functionality available natively in SP calculated columns, on the SharePoint User Voice forum – https://sharepoint.uservoice.com/forums/282887-customer-feedback-for-sharepoint-server

      There is a simple trick to get over the limitation:
      1. Modify the calculated column and change the returned data type from Single line of text to Number/Currency/Date and Times. Click OK.
      2. Change the value to something like the following: ="<a href='https://www.bing.com/search?q="&[Search Term]&"' rel="nofollow">Search</a>"

  • Bob Stansfield

    Reply

    Hi Keith,
    I need to average a text field in SharePoint that has a measure of time (example format 1:23 would be entered in field to represent 1 hour and 23 minutes) I have not found a way for the user to enter that in a “number” field in that format, or I could average easily. Any thoughts on how I can get a list of times in this format in a sharepoint list to have an average of all the times entered?

  • Yugeswaran Vasudevon

    Reply

    I’ve a column which only need to enter positive integer with two decimal values. actually the column is unit price. How we can set validation for that?

  • Praveen

    Reply

    Hi Keith ,

    This page has been very helpful to me in understanding many things since im a fresher .
    Though I came across a situation for which i could not find an appropriate solution .

    A form that takes the created date and should give the next working day of that week and also skipping the weekend in doing so.
    please help

    Thanks
    Praveen

  • Praveen

    Reply

    Hi Keith,

    I am working on a form that should take the created date of a document and calculate the next working day excluding the weekends.
    Please help me out with an appropriate logic or formula to calculate .

    Thanks
    Praveen

    • Keith Tuomi

      Reply

      Hi Praveen,

      One of the calculated column functions you can use in SharePoint is Weekday which returns 1 for Sunday, 2 for Monday and on through to 7 for Saturday.

      To reflect a typical Mon-Fri work week, we can put this into a formula for SharePoint calculated columns like this:

      =IF(Weekday([Created])>3, [Created]+5, IF(Weekday([[Created])>1,[Date Received]

  • Marco

    Reply

    Hi can you help me with my sharepoint form. I have 2 date picker and 1 text box. the process is date picker 1 + text box 1 = date pincer 2 but exclude weekend Ex. Date picker 1 (3/18/2016) + textbox 1 (3) = date picker 3 ( 3/23/2016) excludes weekends

  • Buki

    Reply

    Hi

    I am using SharePoint 2013 and designed a form where it calculates the variance % between 2 numbers, which works fine but it displays a lot of decimal places like 93.333333% – whereby ideally should show 93% only (example).

    I used the Calculated Value from sharepoint with the following formular: (PlannedExpenditure-Expenditure)/PlannedExpenditure
    and also tried (PlannedExpenditure-Expenditure)/PlannedExpenditure*100 but no difference.

    cheers

  • shankar

    Reply

    I’m having column where user will enter their input as abc-defgh/username or they may enter only username. If they didn’t enter their domain, manually i need to add that domain.

    If suppose domain was there then i have to ensure before “/” everything should be in Uppercase and another validation i have to do is after “/” everything should be in lowercase.. !!

    i should not add any extra columns.. Plzz suggest some answers ?????

  • Joister

    Reply

    Keith, working on a Time Sheet custom list, after few ups/downs with the date and time fields, we decided to create single line of text columns, one for hours and one for minutes, was able to validate with =ISNUMBER([MyField]+0) and length using SharePoint OOTB max characters to 2, but now how can I restrict to only enter values from 00 to 24 for the hours and 00 to 59 for the minutes columns?

  • christophe

    Reply

    Hi Keith,

    I need to do some operation bitween a contract_expiry_date and notice_period in month

    contract_expiry_date is a date field
    notice_period is a Number field

    if notice_period is not empty then contract_expiry_date-notice_period in month else contract_expiry_date

    May you help me

    • Keith Tuomi

      Reply

      Having hung out with the content writers for TechNet/MS Office help etc. I came to learn that they are VERY interested in making useful, helpful documentation. I think sometimes the volume of material and the constant stream of new tech means they get assigned to different priority areas. In the last years MS has become more receptive to feedback like this, and there’s places like https://excel.uservoice.com/ where people can say things like “hey, you’re totally missing a page that completely documents Excel functions in an easy to navigate way”.

  • Manuel Rosas

    Reply

    I’m creating a customized list for staff to input their working hours, all columns are working except my Overtime calculated column if my total week hours are over 40. Columns are:

    1.Week WH = Week Working Hours formula: =TEXT([Mon WH]+[Tue WH]+[Wed WH]+[Thu WH]+[Fri WH],”[h]:mm”)

    2.Week OWH = Week Overtime Working Hours formula: =IF([Week WH]>=”40:00″,TEXT([Week WH]-“40:00″,”hh:mm”),”00:00″)

    My total “Week WH” columns is giving me a total of “46:30”, so I need to calculate the “Week OWH” to be the difference after “40:00” and the result should be “6:30”

  • Billy Gutierrez

    Reply

    I am trying to validate a date column, where the execution date requested cannot be less than 14 days from today or the created by date? I cannot quite figure out the formula. Thanks in advance

    • Keith Tuomi

      Reply

      Hey Larry,

      Replace [Item Name] with the field name that contains the name and [Item Number] with the field name that contains the item number.

      =CONCATENATE([Item Name],”-“,REPT(“0”,2-LEN([Item Number])),[Item Number])

      The REPT function will repeat a given string a specified number of times. By subtracting the length of the number from 2, you tell it how many leading zeros were needed.

      One caveat to this is that I’m not sure if it works with numbers longer than the digit count we want (in this case, greater than 99). I’m not sure of the behavior of REPT with negative numbers passed in. If you need to, you could add an IF function that checks if it is negative and uses zero if it is.

Leave a Reply to Keith Tuomi Cancel reply

Your email address will not be published. Required fields are marked *