60

Update 6/2017: Do read: June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back ## Calculated Column documentation: https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List #Me in a Calculated Column You can not use [Me] in a Calculated Column Formula. You can use [Me]...


30

Sorry, with http://www.ViewMaster365.com/#How I have been an avid promotor of the undocumented HTML trick (worked since SP2010, maybe even SP2007) Like you I am amazed Microsoft actually disabled it, then again it already was disabled for Modern Experiences, so it was clear the HTML trick had no future Must be kismet or faith or karma or just plain common ...


20

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.


15

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.


14

By far the easiest method to do this is to simply set the "data type returned from this formula" setting on your column to be "Number". Against all logic, this interprets your formula as HTML. I'm not sure if this works in 2007, but it certainly works in 2010 and 2013. Since there are 10,000 views on this topic and very few up votes, I'm guessing this is a ...


14

Try to use this formula to get Year without fractions =TEXT([Created],"yyyy") Output


13

Below function gives you month in digit. =TEXT([Date],"mm") => returns 01 if date is 15-Jan-2014 Below function gives you month name =TEXT([Date],"mmmm") => returns January if date is 15-Jan-2014 Below function gives you short month name =TEXT([Date],"mmm") => returns Jan if date is 15-Jan-2014


12

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


12

Try the following formula =TEXT(Created-Closed,"h") [Output] In case you need to show the exactly Hours and minutes, try this =TEXT(Created-Closed,"hh:mm") [Output] Regarding Days and Hours and Minutes, try the below one =DATEDIF(Created,Closed,"d") &" days .."& TEXT(Created-Closed,"hh:mm") [Output]


10

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


10

Note: in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column That means given answers may not apply for newer SharePoint versions For long explanation and work arounds see: June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back Original answer: If you want ...


10

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.


9

Try to use this one =TEXT([DateField];"mmmm"). I usually have error if use ',' instead of ';' in formula. My site language is Russian and it then translates automatically if I write first in English. Reference: Get the month name in calculated column


9

It is definitely not a bug, I don't see anything strange or wrong in this behavior. Yes/No fields are internally stored as bit/boolean. So your first attempt of comparing the value to a string "Yes", is correctly evaluating to false, then resulting in "Print some other text" returned. If you use this formula, then a boolean comparison will be performed, ...


8

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


8

Check if one of the value is empty, if it is, return blank, else calculate the difference. =IF(OR(ISBLANK([Date 1]),ISBLANK([Date 2])),"",DATEDIF([Date 1],[Date 2],"D"))


8

The new way of displaying HTML based on data in list(item) was possible using using JSLink. However the 'new' way to do this is by using SPFx Extensions. It's currently in preview. You should use a FieldCustomizers. As MS states: "it can be used to provide modified views to data for fields within a list.". You could start here for more information.


7

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


7

The formula for calculating hours between two times is =TEXT([Column2]-[Column1],"h")


7

You can put in a support ticket to request an extension of the feature until 9/10. While it's not a long-term solution, this will hopefully give Microsoft enough time to enable the admin setting that is available for the on-prem version.


7

The short answer on "How to get the same functionality back": Third party products. Client-side rendering via JS Link property of web parts. This only works for Classic mode. If you are using Classic mode, this is likely the 'best' short term answer to broken interfaces until a longer-term solutions is implemented. SPFx Extensions or customization ...


6

I tried this in SharePoint 2013 Online but didn't work ="<a href=\"https://portal/sites/search/Pages/results.aspx?k="&[Document ID Value]&"\">Link To Document</a>" It doesn't like the \". As a workaround use single quotes for the URL as follows ="<a href='https://portal/sites/search/Pages/results.aspx?k="&[Document ID Value]&...


6

If you only use this for display purposes, why not use a different approach which does not require you to do updates on the list item at all.... Create the site column in a sandboxed solution. Your column stores the Due date. Create a javascript file to deploy somewhere to your site, e.g. /Documents/duedate.js Configure the JSLink property in the site ...


6

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


6

What I did to get around this in SP Online, is to use JSLINK to render the HTML. This is a much more elegant solution, and easier to use than the calculated columns anyway. But I was caught completely off guard by this.


6

You can use 'Group By' to group (and sum) your Keyboard rows and your Camera rows. Go in List Settings>Modify View>scroll down to 'Group By' and select 'Hardware Type' as the value. You might also want to use 'Totals'. Go in List Settings>Modify View>Totals>choose a field to total by e.g. Hardware Type. You can also use Filters to filter on one Hardware ...


6

There are two issues. 1. Calculated columns are not synchronized with other columns' data, so when the version-column's value changes, the calculated field doesn't understand to re-calculate its status until there's a save-action either for the calculated field or for the document itself. With lists the same could be "handled" by opening the list item into ...


5

If the column name is "NoSpace", the validation formula is: =ISERROR(FIND(" ",NoSpace)) This is the NewItem form:


5

There is a calculated column 'trick' where you return a number type but use regular string concatenation and an html 'a' tag and the item will show up for you as a link Something like ="<a href='https://www.google.com?q='"&[searchColumn]&"'>click here</a>" You should be able to add your target _blank attribute using the above. EDIT: A ...


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


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