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


10

Just to be clear: title is only "mandatory" if you create lists using the GUI. When you provision your own list using code or CAML you can create lists without Title. If you insert the formula =Me&" - "&TEXT(Today,"dd-mm-yyyy") into your calculated value, you should get something close to what you want. Be aware though that at any time the user ...


10

Create Calculated column called "UserName" and type in this formula =LEFT([Email Address],INT(FIND("@",[Email Address])-1)) Create Calculated column called "Domain" and type in this formula =RIGHT([Email Address],LEN([Email Address])-INT(FIND("@",[Email Address]))) It should work.


9

Unfortunately if you are trying to have this always show the current date and time, this won't work since Calculated Columns are not recalculated on viewing, but only on creation and modification. See this article: http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/ However, if you are just trying to extract the time when ...


9

Try to insert =[Me] Also look at my answer to a similar post here: Calculated value for Title field?


9

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


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.


7

SharePoint does not support using today's date in a calculated column and the fields are not recalculated unless a value in the item changes. Here is a solution you could try using SharePoint Designer and conditional formatting: Create another calculated column to hold [Due Date]-3 (e.g. Warning Date) Create your [Almost Due] column and put your message ...


7

Rewrite your formula with if-statements and nest them, something like this: =if(AND([Region1]="A",[Region2]="B"),"NO",if(AND([Region1]="C",[Region2]="D"),"YES","N/A")) The first if does the first test, if that statement doesn't hold: use the second if (nested in the first if).


7

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


6

I believe the date/time format you are looking for is: "dd mmm yyyy h:mm AM/PM" This may be useful: TEXT function reference


6

Unfortunately, calculated column approach will not work as expected :( Calculated fields are updated only if the column itself is being updated, or the item is updated. You can fiddle with setting date to future in Windows to prove it. So I would recommend you to use XSL transformations for XsltListViewWebPart in conjunction with dateUtils.xsl from Marc D ...


6

elcapitano, my tutorial should work: http://blog.pathtosharepoint.com/2008/09/01/using-calculated-columns-to-write-html/ A couple notes: in the above post, you'll find a link to a troubleshooting page there are several versions of the Text to HTML script, I recommend that you pick the latest one (v 2.1.1) here: ...


6

Don't think you can do it with a calculated column, but you can use a list workflow to set a text field. Add a Set Field in Current Item action. Configure the value with User Profiles as data source and select the Name field from source. Then match the Account Name field (only option available) to the column with your User ID (assuming that the user id is ...


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

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


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

In the Default option you can use [Today] plus some number of days... you would have to find the weekday as an integer, compare it to the weekday of Today as an integer, and do some math to get something to add to [Today] to make it next Friday. I'll see if i can find the right formula for it in the mean time. Edit: ok so you can use WEEKDAY([Today]) to ...


5

Partly inspired by this question, and partly because we developed a product that has this functionality, I've written a blog post (linked at the bottom) on how to achieve this. The summary is: JavaScript: Use a Content Editor Web Part. Designer: Create a custom view in SharePoint Designer (as already proposed by @Laurie). Code: Make your own custom field ...


5

You can't use "volatile" functions like [Today] in calculated fields. The reason is that the values of calculated fields are only computed when the list item is created or updated, so unfortunately it is not possible to do what you require using a calculated field. You can "trick" SharePoint into allowing a [Today] expression in a calculated field by ...


5

You can add a calculated column to your list for [Effort] * [Percent Complete]. Unfortunately, you cannot create a view and select that calculated column for a Sum under Total. You can, however, create a Data View Web Part using SharePoint Designer and add the sum that way. Here is a post on how to Get the sum of a calculated column.


5

There's an attribute you add to xsl:value-of called "disable-output-escaping". So change your line to look like this: <xsl:value-of select="@MyColumn" disable-output-escaping="yes"/>


5

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


5

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


4

The problem is that calculated columns don't recalculate until an item is modified. So if nobody edits an item for 3 days after post-op, your calculated column will still show 0. You have 2 options within SharePoint: either show the op date and let your users calculate it in their heads, or create views that filter into 0-1 days post-op, 1-2 days post-op, ...


4

I used the following formula. Week starts on monday. Depending on your regional settings you have to replace the ; with , =IF(INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7)=0;52;INT((StartDate-DATE(YEAR(StartDate);1;1)+(TEXT(WEEKDAY(DATE(YEAR(StartDate);1;1)-1);"d")))/7))


4

You are unable to change the types of Columns in SharePoint lists - simply because it's impossible to guarantee how to transform the data stored in one column type to another. What you can do, is create your Yes/No column, go to DataSheet view, and see if you are able to copy/paste the values from your calculated column over to the new Yes/No column.



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