Hot answers tagged

24

Me in a Calculated Column I do not understand why you want [Me] in a Calculation. You can use [Me] in de View Filter settings Like the [Created By] and [Modified By] Columns and Lookup Columns [Me] is not available for Calculated Columns as the textual information is not stored in the List Item. I presume its legacy behaviour. Although with todays ...


10

If you want the delta values displayed in a View like this: You must create a View that Groups by Room and sorts by Date A Calculated Column 'ChangeMonitor' with Formula set to datatype=Number to output HTML/JS can then use JavaScript code to calculate, display and color the deltas Copy/paste this Formula to your Calculated Column Formula: ...


8

I think, =ROUNDDOWN([Column1],0) should work nicely. See - https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx


7

The built in [Today] and [Me] values have never worked in calculated columns, all references to this in previous versions were "hacks" as Sane mentions. The best ways to do this would be either via workflow on item creation/edit you update a fields based on the formula you desire or you could try a remote event receiver to handle it.


6

To average multiple columns in an item, you'd use a calculated column such as =AVERAGE([Column1],[Column2],[Column3]). To average a column, edit the view and expand the Totals section. By your column name, if it's a number, you can select average (etc) from the dropdown.


5

On the List tab on the ribbon select Modify View. Scroll down and expand Totals section. Select Sum next to Total Miles column and save the view. You can use it together with groups and sorting. Calculated colum I've created calculated column with result type number and set view aggregation from code. It works fine with COUNT (which is also impossible to ...


5

Calculated Formulas are only modified/updated when an Item Changes Read that one more time: Calculated Formulas are only modified/updated when an Item Changes Got it? That means the Today/Now function will get you the result from: the LAST item update. SharePoints Today/Now functions do not behave the same as in Excel. You can force such an update ...


4

The Number trick to display HTML only works in Views, it shows the bare HTML on Forms and in Alerts. Full explanation and pros & cons at: http://viewmaster365.com/index.html#/How For Forms you have to go for Client-Side-Rendering (CSR) BTW Your Formula: =IF(Area="HR" ;"<div><img style='float: left' ...


4

You are asking for a No SharePoint Designer and SharePoint 2010 solution. It can be done in a Calculated Column without the use of any other Webparts like documented on the blogs the other answers refer to. The technique is documented at http://www.viewmaster365.com/#/How The cons of this approach are also documented. But it ain't easy; Calculated ...


4

I reformatted the Formula in your original post, you can copy/paste multilines and SharePoint will ignore them. The = was allready spotted; You are also missing a False in your second IF =IF([Course #]="AWR135" ,"Promoting Community Cybersecurity Planning" ,IF([Course #]="AWR136" ,"Essentials of Community Cybersecurity" ,"" ...


4

I presume you are on an english environment, so the separator is , (comma) and not ; (semi-colon) =DATE(YEAR([Last updated]), MONTH([Last updated])+3, DAY([Last updated]) ) More functions and syntax: http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List


3

It is true that I need to go for CSR. I went into the edit mode on the DispForm view for one of my items. Go into the Web Part attributes -> Miscellaneous Enter the JSLink to your file (in my case it is ~sitecollection/SiteAssets/logo.js) Sharepoint is showing my HTML code as a text string and for this I simply exchanged the innerHTML with my innerText. ...


3

Formula will be something like this: =IF(OR([Column3]="5", [Column3]="6"), [director approval date]-[finance approval date], "-") Here is the reference Example of common formulas


3

You can use a calculated column for this. Set the return type to return as number. Create a calculated column and add this formula: ="<img src='/_layouts/images/blank.gif' onload=""{" &" var day=new Date();" &" var n = day.getTime();" &" this.parentNode.innerHTML= n;" &"}"">" But this will give you milliseconds. You can get the ...


3

Date columns are essentially numbers starting at 30/12/1899 So besides adding one day, you have to display it as a date again (when using it in a Calculated Column datatype=Text) =TEXT( [Due Date]+1 , "dd/mm/yyyy" ) Or set the datatype of the Calculated Column to Date =[Due Date]+1 Adding one day correctly counts the new date ...


3

Let's say, six columns are C1, C2, C3, C4, C5, C6. So now you can just check the value before adding and dividing. I mean following =( IF(C1="NA",0,C1) + IF(C2="NA",0,C2) + IF(C3="NA",0,C3) + IF(C4="NA",0,C4) + IF(C5="NA",0,C5) + IF(C6="NA",0,C6) )/ ( IF(C1="NA",0,1) + IF(C2="NA",0,1) + IF(C3="NA",0,1) + IF(C4="NA",0,1) + ...


3

Values in calculated columns are not dynamic. They are recalculated when list item is updated.


3

Regarding the error you are receiving, if you are using different regional settings than English in your site, try to replace all the commas (,) to semicolons (;). You could make a easier calculation to. This one returns the number of days, hours and minutes between the 2 dates. =TEXT([Fin Diag]-[Debut Diag];"d:h:mm") Update Tried out your calculation ...


2

It is blowing up on the MID function, but not because it is not supported. You are using MID([IP address]) and only declared one parameter. The MSDN link in the other answer indeed mentions the MID function is not supported, but even Microsoft can produce incorrect documentation. The MID function is supported at least since SharePoint 2007. see: ...


2

You don't need a calculated column for this. Just use your existing [DueDate] column twice in your view filter: Show the items when column DueDate is greater than or equal to [Today] and when column DueDate is less than or equal to [Today]+5


2

You cannot directly specify today and me in calculated column to set the values. You will have to create columns named Today and Me and then use it in your calculated column. Once you are done with the formula for the calculated column, you need to delete the 2 columns created for this. I think this will make your things work.


2

You cannot use it as a number. You have to "convert" your value to numeric in order to make the validation, otherwise it will be "false". You can use the VALUE function to convert a text representation of a number, or simply add a number to your value to get it interpreted as numeric. =IF([ChoiceField1]+0<=2, 0, {nested IF if false}) or ...


2

You can accomplish this with a calculated field and a view. Create a calculated field named Month and use the Date In or Date Out field (which ever you want to use to count occurrences). The calculation for this column will be =TEXT([DateField], "mm"). This will return the month as a numerical value. Now that you have this, create a view or modify an ...


2

In Excel this can be solved with =SUBSTITUTE(). However, this formula is not available in SharePoint and you have to use =FIND() and =REPLACE() to replace =SUBSTITUTE(). If you only have one & (question is implying that this is the case), this can be easily achieved with =REPLACE([column];FIND("&";[column]);1;"and"). If you have to replace more than ...


2

The formula will be something like this. I have not tested it, but it should work: =IF( AND([Column A]="High",[Column b]="High") ,"Red" ,IF( AND([Column A]="High",[Column b]="Low") ,"Gold" ,IF( AND([Column A]="Low",[Column b]="High") ,"Green" ,IF( AND([Column ...


2

Open SharePoint Designer. Open the website that contains your list with the calculated column. Click on "Lists and Libraries" in the left column (under Site Objects). On the right, click on the list that contains your calculated column. Under "Content Types", click on the content type where you want to hide the calculated column In the content type screen, ...


2

Regarding the accepted answer: Stop linking to outdated and incorrect information about creating a [Today] column, linking to it and then deleting to it. It was allready explained in 2008 By Christophe it just does not work... For a complete explanation read: How to use Today and Me in Calculated column


2

In English language environments no problem, in other languages I can not get it to work, no one has answered my question yet: How to save a Calculated Column Formula in Language Localized notation using REST or JSOM Scriptfile Extract the JSOM code (the REST version is way more code) you are after or execute in an existing Formula (FldEdit.aspx page) ...


2

I reformatted your formula, easier to read and the mentioned quote is more obvious. linebreaks and space will be ignored when you save the Formula. It can be shortened to: =IF(ISBLANK([Potentially Impacted]) ,"Potential Business Impact is to:"&[Potentially Impacted Units]&" " ,"" ) This Bookmarklet helps with checking formulas while ...


2

You can not because a Calculated Formula only operates on ONE Item in ONE List So your workarounds make a workflow copy Min/Max values from List B to every ListItem in List A Use No Calculations; Let the View (using CSR or other scripting) display the incorrect value On every change Min/Max in List B, update the Formula in List A definition with those ...



Only top voted, non community-wiki answers of a minimum length are eligible