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


5

Try this. Create one Calculated column and set data type return from formula as Number. Apply below formula: ="<img src='/_layouts/images/blank.gif' onload=""{"&" var day=new Date();"&" var SPday=new Date(); "&" SPday.setFullYear("&YEAR(Created)&","&MONTH(Created)-1&","&DAY(Created)&");"&" var m = ...


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

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

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

=IF([Fecha01]>[Fecha02],(DATEDIF([Fecha01],[Fecha02],"D")-IF(WEEKDAY([Fecha02])=7,FLOOR((DATEDIF([Fecha01],[Fecha02],"D")+WEEKDAY([Fecha01]))/7,1)*2,FLOOR((DATEDIF([Fecha01],[Fecha02],"D")+WEEKDAY([Fecha01]))/7,1)*2+1)+IF(WEEKDAY([Fecha01])=7,2,1),0)


2

You can use the MAX function and output it in Date format. =MAX([date1],[date2]) If the two dates are blank, it will output a default date in 1899, so maybe put in a IF condition in there.


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

Can you add another calculated column that concatenates the 2 Likelihood + Consequence column's number part as a numeric value, i.e. =VALUE(LEFT(Likelihood,1) + LEFT(Consequence,1)) This would end up with unique numerical values of the concatenated numerical column values, i.e. = VALUE('11') or VALUE('31') or VALUE('12'). Then use the CHOOSE function in ...


2

ISERROR() takes only one parameter and returns TRUE or FALSE. It is typically used inside an IF() statement to evaluate a formula. What you probably want is something like =if(ISERROR(DATEDIF([DATE1] , [DATE2] ,"d")),"0", DATEDIF([DATE1] , [DATE2] ,"d")) In words: If the DATEDIF function returns an error, use "0", else use the result of the DATEDIF ...


2

Best is that use ID column. It is not possible with only calculated column option. You have to set workflow for auto increment value.


2

=MONTH( "January" ) will not return a value because SharePoint doesn't have a clue what you meant You have to convert the Month TEXT to a valid Number (january=1 in SharePoint) IF([column]="January" , 1 , [column]="Feb2" , 2 , ...


2

You can do it by jslink. Make one js file (ex. ViewMonth.js) and add following code: Replace your column name instead of MonthNum and month (function () { var monthctx = {}; monthctx.Templates = {}; monthctx.Templates.Fields = { "MonthNum": { "View": setmonth } }; ...


2

What you need is a formula at list item validation. Go to list settings, and find validation settings. The formula you need to use should be something like the below one: =IF([Status]="Closed",IF([Date Closed]=Today(),TRUE,FALSE),TRUE) Instead of checking the [Date Closed] to be today only, you might want to just check [Date Closed]>=Today(). Or, if ...


2

One way to achieve this - Assuming data is in the correct format in all cases =LEFT((MID(CountryWF,FIND(":",CountryWF)+1,LEN(CountryWF))), FIND(":",(MID(CountryWF,FIND(":",CountryWF)+1,LEN(CountryWF))))-1)


2

There is no "formula" that can send an email. You can use a formula to calculate a value in a column, and that value can look like an email address. Such a formula might look like =IF([ChoiceField]="jack","jack@abc.com",IF([ChoiceField]="bob","bob@abc.com","tom@abc.com")) If you have more than three choices you need to nest more IFs. If you want to send ...



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