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^364 /, +, -, * 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)+15 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
- The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or references that contain logical values.
- If an array or reference argument contains text or empty cells, those values are ignored.
- If the specified range contains no logical values, OR returns the #VALUE! error value.
- You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.
-
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-146641Make sure your formula is not capping out on one of the following limits:
Formula length: 1024 characters
Calculation length: 2048 characters
Filter length: 256 charactersYou can avoid broken formulas by adhering to this basic Excel guidance (only SharePoint-related sections listed below):
- Start every function with an equal sign (=)
- Match all opening and closing parentheses
- Enter all required arguments
- Use unformatted numbers in formulas
- Use the right cell data type (in the SharePoint realm, this would mean, use the right List/Library Column type)
- Use the * symbol to multiply numbers
- Use quotation marks around text in formulas
- Don’t nest more than 64 functions in a formula
- Enclose sheet names in single quotation marks
- Avoid dividing numeric values by zero
- Avoid deleting data that’s referenced in formulas
- Watch formulas and their results
- Evaluate complex formulas one step at a time
- Use error checking to detect errors
SharePoint Calculated Column Formulas & Functions – The Essential Guide
I've been low-key stressed about how to view the many email inboxes I have in Outlook in one view without having to scroll and hunt for new messages.
The 'macro' method in this video works great.
View Multiple Inboxes at Once in Outlook 365 https://youtu.be/NpFSsovHgVo via @YouTube
M365 Community Content https://tuomi.ca/2022/05/07/m365-community-content/
Leverage Microsoft Project for the web with the Project Accelerator https://youtu.be/Hxr8dncHmT8 via @YouTube
150 responses to “SharePoint Calculated Column Formulas & Functions – The Essential Guide”
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.
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
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
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.
Keith
Thank you, the above has solved my issue regarding commas!!!
Great work thanks gain.
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.
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.
I believe this layout glitch has been resolved. Thanks again for pointing it out.
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!
Hey Kacey,
Try the trick at http://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/. You can overcome the issue of it not liking to use a “volatile” calculation like [Today] in the formula, by creating a separate SharePoint column called “Today” and setting it to default to todays date.
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.
Strange, as I tried your original formula and the ISBLANK worked ok.
Another method could be something like:
=IF([Release Date]=””,[Today],[Release Date])
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.
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.
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
Hi Martyn,
Try:
=DATE(YEAR(TODAY()),MONTH(TODAY()),20)
Perfect, Thanks Keith
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
Please check out the first approach described in detail at http://dlairman.wordpress.com/2011/01/10/add-a-unique-auto-incrementing-column-to-a-sharepoint-list/
Keith –
Thanks for pointing Prasad to my post!
Jim
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
Hi Brian,
Have a look at this article, it.. sums.. it up best – http://spreadsheets.about.com/od/excel2010functions/qt/091223-nesting-functions-excel.htm
First time to your site. Great content, helpful, thank you. But can’t stand the UI, frustrating and difficult.
I take it back, my browser was doing something funky. Looks fine now.
Great! Your post helped me in makign a complex formula. Used almost 5-6 functions for my calculated column 🙂
Good to hear, glad to help.
Thanks, very useful!
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
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
How to convert list forms to infopath forms??
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
thanks bro for helping….
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!!
Hi Christy, As you may have already found, if you go to modify the view and expect to see your Calculated columns available in the “Total” section, you will be disappointed as it’s not available through the GUI to add totals of such columns to the view.
To achieve your goal, you’d need to create a data view web part using designer: http://chanakyajayabalan.wordpress.com/2010/07/01/summing-calculated-columns-in-group-by-in-sharepoint-list-dataview-webpart/
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.
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. 🙂
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
I think you’re looking for DATEDIF:
=DATEDIF([Start Date], [Due Date],”d”)
I’ve used the DATEDIF to get number of days between 2 columns till expiration it works great until it goes to past due date like 3 days it shows #NUM! instead of -3
is there any way to show the negative number
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!
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.
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.
Hey Richard, what I would do is forget about SharePoint as the method of booking the room and instead leverage Exchange’s out of the box capability to book rooms as a resource. See http://exchangeserverpro.com/exchange-server-2010-room-mailboxes-step-by-step-guide/
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?
Hi Tracie,
Sorry i’m not sure if your code is coming through in the blog commenting system. Are you trying a technique like described at http://sarahlhaase.wordpress.com/2012/06/06/using-calculated-columns-to-add-color-coding-to-your-sharepoint-lists/ and it’s not working?
Hi Keith,
Is there anyway to increment number (in sequential order)without using workflow and will not depending on ID.
This probably isn’t the answer you’re probably looking for (as it’s a bit more complex and requires code), but you can use an Event Receiver to achieve your goal. See the second bit of code at http://faisalrafique.wordpress.com/2011/03/19/creating-auto-increment-field-in-sharepoint-list/
If you’re wanting to achieve your goal strictly with Calculated Columns formula syntax – check out https://www.nothingbutsharepoint.com/sites/eusp/Pages/sharepoint-how-to-create-an-auto-incrementing-number-field-for-use-in-a-custom-id-part-1.aspx
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
Try this:
=AND(LEN([Client File No])>=6,LEN([Client File No])<=8)
Thanks Keith. Appreciate your help. Its working. I have few more SharePoint query, will get back to you.
Thanks again
How can I take First Name from the Created By column to another calculated column?
I don’t think you can – Created By is not available as a column when you create your formula. Next best bet would be to use Custom XSLT.
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
hallo Laura,
Prevent users from changing title:
There are many ways to make a SharePoint field read-only, including:
1. Using PowerShell to set SharePoint column read only.
2. Using jQuery/Javascript to make read only fields
3. Using SharePoint Designer to make SharePoint list field read only.
See: http://www.sharepointdiary.com/2013/07/how-to-make-sharepoint-list-column-form-field-read-only.html#ixzz2aH3LlOvB
Maximum of 10 numbers in a field:
This one was already addressed in the Comments on this blog post, but here it is again:
=AND(LEN([MyFieldName])>=1,LEN([MyFieldName])<=10)
Cascading Dropdowns
The simplest method would be by using InfoPath Forms. Please review this article- I have used this technique to successfully make cascading dropdowns in InfoPath: http://blogs.msdn.com/b/bharatgupta/archive/2013/03/07/create-cascading-dropdown-in-browser-enabled-infopath-form-using-infopath-2010.aspx
To get better with SharePoint formulas I recommend getting very good with Excel formulas- as mentioned Excel formula syntax is mostly support in SharePoint. The best place to start for that would be the F1 help in the MS Excel application/MSDN Documentation.
tschüß
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.
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/
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?
Hey Roberto,
In the column validation setting for Employee ID, use the following formula : =AND(LEN([Employee ID])>0,LEN([Employee ID])<11)
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
Hi Yamuna,
Here you go:
=mid(left([Title],find(“)”,[Title])-1),find(“(“,[Title])+1,len([Title]))
Thank you 🙂 its working fine..excellent
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?
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:
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.
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
Hey Kane,
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/
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
Hi Keith,
Thanks for your reply, I have had a look at the posts you mentioned but I am still struggling to set up my sharepoint list as desired. Sorry I am a bit of a newbie at this, and have not been able to wrap my head around sharepoint just yet.
Ideally what I want to do is illustrate the due date & time (days; hours; mins) of a task based on its SLA. eg. Lets say the SLA for an issue is 4 (business) hours, if the issue is logged at 4pm on a Thursday then it would be due at 12pm Friday (1 hour on Thursday & 3 hours the following day), but If it is logged at 2pm on a Friday then it would be due at 10am on the Monday (3 hours on Friday + 1 on Monday, skipping the weekends as they are not “working days”).
Any additional advice you or anyone else can give me would be much appreciated.
Regards,
Kane
Don’t worry, I often am unable to wrap my head around SharePoint too. 😉
It really does sound like what you need a is Workflow. A workflow let’s you perform business logic like you described – check out the getting started guide at http://msdn.microsoft.com/en-us/library/office/jj163917(v=office.15).aspx. SharePoint includes Workflow out of the box, all you need to do to get started is download a free copy of SharePoint Designer.
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
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"
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)
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!
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
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.
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..
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.
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
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
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.
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
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!
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!
Thanks – I had adjusted the blog theme but didn’t catch that this page in particular had become practically illegible. 😛
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
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.
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.
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?
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
This formula will show LOW if VALUE is less than 3, and show HIGH if VALUE is greater than seven.
=IF(Value7,"High"))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
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))
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!
How do you calculate due date from date received, excluding weekends and ideally holidays? Thanks.
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.
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.
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.
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!
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
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″))))
Thanks Keith, I have no idea how I missed your 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?
Try checking for the ISBLANK condition and then selectively applying the date format based on that outcome:
=IF(ISBLANK(TEXT([DOB])),””,TEXT([DOB],”MMMM”))
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
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
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?
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?
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]
You can concatenate strings and numbers in a variety of ways:
=CONCATENATE(Leave Description,” “,MAPI Contact)
=(Leave Description & ” ” & MAPI Contact)
=(Leave Description + ” ” + MAPI Contact)
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?
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
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.
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)
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?
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?
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.
Glad I found this, very useful! Thank you Keith.
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?
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
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.
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.
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.
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>"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?
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?
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
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
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]
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
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
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 ?????
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?
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
Here’s a question: why doesn’t Microsoft have a page like this? Good stuff.
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”.
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”
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
Is there a way to take a number – like the ID value – and format it to 5 digits? In other words, I need 00001, not 1, as a text string to use in a calculation column.
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.
Thank you for your suggestion. Here’s where I am so far:
=CONCATENATE(
TEXT(Created,”yyyymmdd”),
“-“,
REPT(“0”,
5-LEN([ID]) ),
[ID]
)
But when I attempt to save the formula, I get an error saying there is a syntax error or it is not supported.
It seems like the 4 things should be able to be concatenated together.
Larry you are correct, you can concatenate multiple strings together. I think your problem is the use of the TEXT function, which only takes two paramaters: the value you want to format, the format you want to apply.
If you look at parenthesis around your TEXT function you’ll see it’s not closed off properly.
Hi Keith,
Is there any opportunity to help me with a formula to calculate the hours between two times for a calculated field in SharePoint 2010?-preferably excluding weekends?
Hi Alex, you could use a formula like this to get the basis for excluding weekends:
=IF(WEEKDAY(StartDate)<3,StartDate+4,IF(WEEKDAY(StartDate)<7,StartDate+6,StartDate+5))
If the start date is a Sunday or a Monday, we need to add 4 calendar days. For Tues through to Fri we need to add 6 calendar days to accommodate the weekend and on a Saturday, 5 calendar days will be added.
Great Post! Can you use a nested if then statement in a normal field (not a calculated field). For instance like this:
}
=if(
[$Status]==’In Progress’,
if (
[$Status]==’In Progress’,
“elmType”: “button”,
if(
[$Status]==’In Review’,
“elmType”: “button”
)
)
)
}
I want one field with two types of buttons to start different workflows.
Hi Martin, you couldn’t do this in a non-calculated field directly but you could use SharePoint display templates (in classic lists/libraries): https://docs.microsoft.com/en-us/sharepoint/dev/general-development/sharepoint-design-manager-display-templates
A specific example can be found here: https://sharepoint.stackexchange.com/questions/118290/conditional-formatting-in-display-template-in-sharepoint-2013
Hlo everyone
I m using SharePoint 2013 and I want to put validation in custom list column to change text Red colour when value of that column is equal to some value . I m new to SharePoint kindly help me
Regards
Hey Sandeep, if you are using a modern SharePoint list you can use the solution from https://sharepointmaven.com/how-to-color-code-and-format-lists-and-libraries-in-sharepoint-online/
how can I change/edit a ‘text’ type column to ‘calculated’ type. I get other data types as option when I go to edit columns but not ‘calculated’. I am only getting calculated as option when I create new column but not when I edit a created one. thank you in advance!
Hi Yohannes, Unfortunately you’d have to create the Calculated Column from scratch, there’s no way to convert text over to calculated. Probably something in the way the data is stored in the backend.
Hi Keith
This maybe confusing…..please bare with me. I have 3 columns that I am trying to use in this calculation 1.Target Completion Date, 2. Date Closed, 3.Days Past Due, 4.Today. I would like to have the “Days Past Due” column be able to give 2 different values depending on certain situations. 1 situation if a date is input into column “Date Closed” then the value would be the calculated days from “Target Completion Date” to “Date Closed”. If there is no value input into “Date Closed” Then the Value in the “Days Past Due” will be the calculated days from “Target Completion Date” and “Today” (Which is todays date, that column has formula Today())
I hope this makes sense and I hope you can help, I have been trying to figure this out and have has no success.
Any help would be greatly appreciated. Thanks in advance
Hey Jeff,
The requirements make sense, I think there’s not much to this other than figuring out the nesting of some IF statements.
Here’s a really good resource for helping prototype formulas in Excel (which then can usually be transplanted as Calculated Column formulas) : https://www.myonlinetraininghub.com/if-formula-builder
Give that a try and see if it helps.
[…] popular blog posts is originally from 2012 and focuses on SharePoint Calculated Column formulas: SharePoint Calculated Column Formulas & Functions – The Essential Guide – Tuomi Services…While many of the questions people ask in the comments are asking about “novel” formulas […]