Hot answers tagged

16

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


6

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.


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 ...


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


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 ...


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

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

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.


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

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 ...


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

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

I tried it as the following example in my SharePoint 2013 on-prem. If you want to use the today() date, the formular looks like this: =IF(AND([Due Date]>Today(), [Due Date]<Today()+7), "Requires Action", "")&IF(Today()+7<[Due Date],"In Progress", "")&IF([Due Date]<=Today(),"Close Out", "") And my list looks e.G. like this: Keep in ...


2

If you have not wrapped a attribute string in quotes the browser will cut off at the first space So add single quotes wrapping the href attribute like you have done for src= and style= ofcourse problems will occur if the Title itself has a single quote, so this solution is not failsave. Update #1 ="<a ...


2

Assuming you want to have a count of whether or not a list item's text column has a value, you can use the Totals properties of a view. Select the text field and then choose "Count" for the each field you require. Then, eliminate all fields from this view definition except those fields you are counting. And finally, you'll need to apply CSS (to the view ...


2

Considering that the only times a "start date" may then change is when an item is inserted or the item preceding it is changed, I would try and see if you goal can just be achieved by using item based event handlers. An item added event could be used to retrieve the end date of the item preceding the one you are adding, and then setting the "start date" of ...


2

In this line $_["Today"] = [System.DateTime] apart from the “ vs " quotes (which I believe are OK in your script as per the output), you need to assign a date object e.g. $_["Today"] = [System.DateTime]::Today. The [System.DateTime] expression represents the DateTime class, but it's not an instace of it. Apart from that, the $list.Update() is only ...


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 ...


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

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

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

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



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