.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

How to validate the value of date columns and use its value in the Calculated column

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :C#


I am using SharePoint 2010. I have 3 columns in the list:
1) Start date
2) End date
3) Description

I have 3 following scenarios:

·         Some records have value of only Start date and "Description"

·         Some records have value of only End Date and "Description"


View Complete Post

More Related Resource Links

Weird Date vs Date/Time issue using a calculated column

I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

Calculated Column: Text ==> Date and Time Column (Date+Time)





I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)


How can I have a calculated column which is a Date+Time based on a text_column

Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time



20070629_0848 converting to 29/06/2007 08:48


==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)


Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...


It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....





Errors with calculated columns, the column posts, but doesn't calculate.



I have a custom list that isn't processing calculated columns anymore.

When I post a calculated column I get one of two error messages...either "SQL Server may not be started" or "The value does not fall within the expected range".  I've noticed the sql message before but the changes are made and calculations in the past have worked ok.  Now that the "value" error is cropping up it's quit calculating the columns.

i.e. I open a calculated column, simply have it reference a numeric field (=[MyNumberField]).  When I hit ok I get one of the two error messages.  When I look at the value of the calculated column it's blank.

Any references to articles, etc. would be helpful.  There are 406 columns in this list...

Best Regards,


Calculated column based on other columns in Lookup list selection


.. if that makes any sense.

I have a list with with lookup column to another list. that list has other columns.

I want to add a calculated column on the parent list, that set it's value to column from another list, but based on my selection in the lookup column of the parent.

Possible? presuming the calculated column  gets set when I submit and not interactively? what if edit the data and change my selection?


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!


Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

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?




DataGridView>Edit Columns> Column Read Only property is unwilling to change

Hello, I am facing the following mystery: When changing the read only property of a column to false and reopening the "edit columns" window, it mysteriously turns back to true. Even when closing the designer and changing it in the designer code, the resulting execution does not allow edit, as well as when re-opening the designer you see true again.             this.numberDataGridViewTextBoxColumn1.DataPropertyName = "Number";             this.numberDataGridViewTextBoxColumn1.HeaderText = "Number";             this.numberDataGridViewTextBoxColumn1.Name = "numberDataGridViewTextBoxColumn1";             this.numberDataGridViewTextBoxColumn1.ReadOnly = true;//False is required We thought it might be related to data binding. rebinding the grid did not help as well. The is no other place in the code where it changes. Please advice, Thanks

Calculated Date Fields

I'm trying to calculate whether a delivery is on time or late and by how many days. Is there a way to format the column so it show's the result in the number format as opposed to showing up as a date? Negative numbers don't export into excel well.

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.

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

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.

split a column into multiple columns

Hi i have a column with the following values from my source FILENAME ComplianceStatus_ER_06022010.xml ComplianceStatus_AZER_07052010.xml ComplianceStatus_GEL_06022010.xml ComplianceStatus_AFF_05022010.xml ComplianceStatus_Good_2010.xml   I want to split the date into different columns as follows using ssis   FILENAME                            CLIENT      DATE ComplianceStatus_ER_06022010.xml    ER          06022010 ComplianceStatus_AZER_07052010.xml  AZER        07052010 ComplianceStatus_GEL_06022010.xml   GEL         06022010 ComplianceStatus_AFF_05022010.xml   AFF         05022010 ComplianceStatus_Good_2010.xml      Good        2010  

Error when creating PK to a column which has dupliacte columns..

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'table name' and the index name 'PK__column__118068EB'. The duplicate key value is (D1).

using [content type] in calculated column

I want to get the content type in the calculated column. [content type] is available in Moss not able to use that in 2010. Any idea?
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