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


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


9

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


9

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


8

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


7

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


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


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

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


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

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

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


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


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

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


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

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.


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.


4

My colleague has written up the different ways to get this sort of countdown/count up display to work in SharePoint, including JavaScript & CEWP SharePoint Designer and DVWP Visual Studio and custom code 3rd party (disclaimer, one of the options is from my own company) http://blog.pentalogic.net/2011/05/how-to-create-a-countdown-in-a-sharepoint-list/ ...


4

As you google around for this you are going to come across pages telling you you can do this with calculated columns using the [Today] trick. You can't, it doesn't work and isn't updated dynamically - and even if it were [Today] doesn't include a time component so X hrs is out. A couple of options for you Use a CEWP and JavaScript to modify the ...


4

As @PirateEric said, you misplaced the parenthesis. If you are still getting an error after that, here are a couple things to check: your category name (is it [IssueCategory] or [Issue Category]?). your category must be a single choice. Multiple choice or lookup won't work To avoid mistakes, pick the field name from the fields listed in the right column ...


4

Try this formula as default value : =Today+1+(9/24) this should do the trick :) times are stored as fraction, so using this method you can also specify minute, say you want to set default value as 9:30 AM then you can write =Today+1+(9/24)+(30/(24*60))


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



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