.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Sharepoint Calculated column with [Today]

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :SharePoint


I am using Sharepoint 2007 Enterprise edition.

I have two columns called Start Date and End Date (both of datetime type). These columns are used to maintain start date and end date of a contractor in a company. I have a calculated column (called Status) which displays "Active" or "InActive" depending on the below mentioned conditions.

I used this formula :

=IF(OR(OR(AND((Today>[Start Date]),[End Date]=0),AND((Today=[Start Date]),[End Date]=0)),OR(OR(AND((Today<[End Date]),(Today=[Start Date])),AND((Today<[End Date]),(Today>[Start Date]))),AND((Today=[Start Date]),(Today=[End Date])))),"Active","InActive")

This is working but not updating the value correctly. I think Today value is not getting updated dynamically.

I have 5 conditionds:

1. Start Date < Today (Where End Date =0)

or Start Date = Today where (End Date =0)

or Start Date < Today < End Date

or Start Date < Today and Today = End Date

or Start Date < Today and  End Date > Today

Any help is appreciated.

Thanks in advance.

View Complete Post

More Related Resource Links

Sharepoint: In Calculated Column, Code with multiple "IF" condition does not work.

Implemantation:  Calculate expected resolve date exclude "Saturday" and "Sunday"  based on Priority and Severity.Columns are:1. Priority:2. Severity:3. Raised on: Date record createdCode: Below is the code which contains multiple "If" conditions. For quick undersatnding, i separated major If condition. Below code does not work and sharepoint gives me Sytax error message. =IF(OR(Priority="Critical",Severity="Critical"),IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+3,[Raised on]+1),     IF(OR(Priority="HIGH",Severity="HIGH"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+4, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+4,[Raised on]+2)) ,   IF(OR(Priority="MEDIUM",Severity="MEDIUM"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+5, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+5, IF(TEXT(WEEKDAY([Raised on]+3),"dddd")="Saturday",[Raised on]+5,[Raised on]+3)) ) ,   IF(OR(Priority="LOW",Severity="LOW"), IF(TEXT(WEEKDAY([Raised on]+1),"dddd")="Saturday",[Raised on]+6, IF(TEXT(WEEKDAY([Raised on]+2),"dddd")="Saturday",[Raised on]+6, IF(TEXT

Use Sharepoint Calendar Start Time field in calculated column

I'm trying to limit the calendar view to current week using the Sharepoint defined field Start Time. However, when I display the serial number behind the Start Time it looks like it is actually giving me the serial number for the Created Date field. I believe it is because the appointment is reoccurring but I'm hoping there is a way around it. I created a new testdate field and manually entered the date and set those appointments as all day events however they are not reocurring appointments. The testdate returns the correct serial number however the start time returns an incorrect serial number. SerialNumber1=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) SerialNumber2=DATE(YEAR([testdate]),MONTH([testdate]),DAY([testdate])) Start Time  SerialNumber1 Testdate  SerialNumber2 8/31/2010 40420 8/31/2010 40421 8/31/2010* 40405    9/1/2010* 40405    9/2/2010* 40405    9/7/2010 40427 9/7/2010 40428 9/13/2010* 40405    9/14/2010 40434 9/14/2010 40435 9/14/2010* 40405    9/15/2010* 40405    *These are reoccurring appointments scheduled as all day events. The others were entered as one time appointments scheduled as all day events.

Summing a calculated column with comma's in sharepoint designer



Sharepoint designer has the trait of getting goofed up with calculated columns that display with a comma.  I typically deal with this by doing a format-number(number(translate(@MyCalculatedColumn,',','')), ###$%%^formatstuff).  this replaces the comma's with nuthing so it will format as a number.

I'm trying to do a sum now...is this possible?


  <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[contains(@FileRef,$TheIDweWant)]"/>
  <xsl:variable name="zNumMales" select="sum($Rows/@MaleLives)"/>
  <xsl:variable name="zTotalMaleVolume" select="sum($Rows/[number(translate(@MaleVolume,',',''))])"/>

The zNumMales works fine...it's just a number column populated by a workflow.

The zTotalMaleVolume is calculated, has commas so it displays as blank, we need to get rid of the comma's.  the above syntax is upchucked...anyone happen to know what it is?



Today's date Sharepoint column formula

How do I access the today's date in a column formula in Sharpoint List.  The TODAY function doesn't work in calculated columns values.

SharePoint Calculated Column Formula or Using JavaScript/jQuery



I have one issue in creating a calculated column formula:

The idea is:

|	Column1			|	Column2			|	Column3			|
|	Name1			|	(empty)			|	Name1			|
|	(empty)			|	Name2			|	Name2 			|
|	Name3			|	(empty)			|	Name3			|

For example we have 2 columns (Column 1 and Column 2) that may have or may not have a value but at least one of them will have a value.

Column 3 is the calculated column that will check if Column 1 has value and Column 2 is empty(by default Column 2 will be empty because Column 1 has value :)) then the value in Column 3 will be the same value as Column 1. Otheriwse if Column 1 is empty and Column 2 has a value, the Column's 3 value will be the value from Column 2.

Can this be achieved with JavaScript or jQuery ? to fill the column's 3 value based on the other two columns if have or not value?


Duplicate column names from forms in SharePoint lists



I'm in the process of migrating from a WSS 2 site to WSS 3/MOSS. On the old site I used an Infopath 2003 form for absence requests. During the migration I now use Infopath 2007. There wasn't any problem initially when the form was stored with the SharePoint form library. However, when I published the form as a content type from Infopath 2007 and then added that content type to the form library, there are two columns for each piece of data from the form. (One from the content type and one from the form list.) I relinked each old form (list content type) to the new AbsenceRequest content type and removed the original one from the list. However, the duplicate column names are still there. Since they came from the Infopath form, they are not editable or deletable from the SharePoint list management interface. The duplicate column names make it tough to build views since it isn't apparent which column to use in the form building UI. It can be determined form the overview display, but it is very cumbersome.


Does anyone know a way to get rid of the old, absolute, columns?

How to get character count in 'Multiple Text 'column using Calculated Column?


Hi All,

I have a multiple text box in a list

I want

  • A column which lists number of characters in multiple list column example 188, 199 etc
  • Multiple character column must not have more than 200 chars
Is this possible?

Appreciate all the help!


Sharepoint list group by column data ,display in webcontrol like panel or label


I  have a sharepoint list[Managers] with groupby sector column. like below




Each Sector group by column data i want to display in label or panel control.

How to do that ? how to write the logic?



Regards, Mansoor

Preparing for SharePoint 2010 Upgrade Today

The SharePoint product team has provided guidance on a number of items that can help organizations prepare, and Service Pack 2 includes a key tool that can provide additional insight and configuration information. This paper, by Quest SharePoint senior architect and evangelist Joel Oleson, walks you through using this key tool and offers major items to consider as you prepare for a SharePoint 2010 upgrade.

Trying to set Calendar Time Interval Using Calculated Column, but getting Error



So i was trying to use a calculated column that added 7 days to the start date of a calendar item. The calculated column worked fine because i checked with several tests. Then i tried using the Column as the End Time in the Time Interval Setting in the Calendar View. This broke Sharepoint. Not the normal graceful error im using to seeing from Sharepoint but the Server Error Page when a dump happens in a ASP page. I havent enabled the full error page because i assume its not like i can fix it, i dont have the source code.


Does anyone else experience this? If so is it a known problem and being worked on?




dynamic 'Today field' in sharepoint


how to do i get a dynamic 'Today field' in sharepoint?

I found many examples such as this which is completely wrong.  In the example below the [Today] is only calculated on item creation and edit, not every time list item is accessed.
http://blogs.msdn.com/b/sharepointdesigner/archive/2008/08/01/date-functions-in-calculated-fields.aspx .

This is very important my customer wants it done asap.


Thank you.



nothing for members to see

How to read a column of a sharepoint list to a text string field of infopath form?

My infopath form used to read these email addresses from a web service and web.config file. Now i would like to get these user email addresses from a contact list in a sharepoint server. I am trying to do this without writing any codes. Here is what i have done so far: 1. Adding a new "data connection to receive data from SharePoint library or List" where i selected Email Address column of the contact list. 2. I added a new Text Field and define a new rule with "Query using a data connection" defined above. When i try to access the newly created Text field above it always return empty as if the Query attached to it did not executed at all. What did i do wrong? Is there a better way? Thank you in advance for your help.

computed column specification - When is it calculated?

on insert , select...?  It's better than Trigger? thanks.  

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Grid Column in Sharepoint.

I need to create a grid column type for sales team of my company: Please have a look at following image. Grid column type in above example - this column type is just making single choices on each row - I need something which can allow various types of columns inside the grid more like a table. So that I can have columns like Qty(number), Cost(number), From Dept(drop-down), To Dept (drop-down) as column heads against the products - so all info of the products can be inside a single column (instead of multiple items in a list) and yet the info is linked row-wise i.e. qty in first row belongs to product 1 - they are linked for the purpose of filtering records later on. By the way, the responses of this form are saved in a spreadsheet like any normal google form - will email the link to this spread sheet as well. Is this possible? Are there any column features or webparts available for same? Apreciate all help!. -saumil

unable to delete a column from library in sharepoint 2007

Hi When deleting  any column in document library we are getting the below error Unable to validate data.   at System.Web.Configuration.MachineKeySection.GetDecodedData(Byte[] buf, Byte[] modifier, Int32 start, Int32 length, Int32& dataLength)    at System.Web.UI.ObjectStateFormatter.Deserialize(String inputString) Also tried to hide/remove the search content place hoder in master page but nothing works. Removed our custom master page with default master page still no changes are being observed. Thanks & Regards. Prerna
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend