Hot answers tagged

32

Me in a Calculated Column You can not use [Me] in a Calculated Column Formula. 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


8

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


5

The ID field you are trying to use in your Calculated Column is not known until the Item is saved . Hence using it in your calculated column may not give you the required output.You can use an event handler or a workflow to update the list items. My preference would be a simple workflow that runs when the items are created.


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

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' src='http://intranet.contoso.eu/...


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

OnPostRender: function (ctx) { var rows = ctx.ListData.Row; var sum = 0; for (var i = 0; i < rows.length; i++) { var value = rows[i]["FieldInternalName"]; sum = +sum + +value; } alert(sum); } Can be condensed to: OnPostRender: function (ctx) { ...


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

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


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 =TEXT(DATE(2015,12,31+1),...


3

[multiple answers about the [Today] trick are deleted fom this thread] The Create/Delete a [Today] Column trick NEVER works For detailed explanation see long answer below


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) + IF(...


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


3

SharePoint stores dates as integer count starting at 1/1/1900 So =EndDate + 15 gets you 15 days from your EndDate To format Dates different you can use the TEXT() function =TEXT( EndDate+15 , "YYYY/MM/DD" ) Function documentation: http://viewmaster365.com/365coach/#/Calculated_Column_Functions_List


3

You don't have to go the CSR route but some JavaScript is required. You already know the HTML in Calculated Column trick... Let's extend that, the data you are after is in the global ctx object disclaimer; I did not test this exact code, might be typos ="<img src=""/_layouts/images/blank.gif"" onload=""{" &"var TR=this.parentNode.parentNode....


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 =IF(VALUE([...


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 A]="Low",[...


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