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

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

Caculated column formula for Workdays between two dates ? Excluding weekends? DateDiff

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :SharePoint
This gives me the number of days between two days.. great=DATEDIF([Completed],[Issued],"D")is there any way to exclude weekends?

View Complete Post

More Related Resource Links

Caculated column formula for user name Compare

Hi , i  have a formula in sharepoint 2010 (form library->validation setting-> formula $ userdefine message)  example : =username =<"2" user messge  your request is already existing ,,, i have  1fields that user name but i need formula for is user already existing two time we did not allow  3 time i am using of validation setting and formula in sp2010 Regards Raju

Datetime column with [Computed column specification] formula

i have a datetime field in my SQL Server table i dont want to add weekend dates in this column. For this reason i always have to check the calendar. how can i use [Computed column specification] formula so that i never add weekend dates?

How can I calculate the Number of Weekends between two dates

I need to be able to figure out the number of Saturdays and the number of Sundays between two dates.  I know that I can do this by looping through the series of dates between the start date and end date.  Years ago I saw a piece of code that an ex-co worker wrote that accomplished this task using a formula.  The formula was extemely effecient.  Unfortunately I no longer have access to this code.  Does anyone have an algorithm that will perform this calculation that I can make use of?  Thanks

formula validation setting on column sharepoint 2010

Hi, Hi,  I have a two fields(mailid,cost,mailid2), i need a list validation of mailIds if already existing  mails three time I need a validation for new request , i need a formula for column in validation setting ->formula in SP2010  if mail is already existing validation  i need formula for validation setting for list it's new feature in sharepoint 2010 Regards

Lookup column uses ID instead of Title in rule formula

I have two SP lists: Partner and Request. Partner just has a Partner Name column, plus the ID column. In Request, I have a lookup column pointing to the Partner list. There is also a Title column and a Request Date column. When I set up the lookup I set it to get info from the Partner list’s Partner Name column, not the ID column.   I made an InfoPath form to make a new Request, with the Partner being selected from a Drop-Down List Box. When this form is submitted, I would like to change the Title field to be a string concatenation of Partner Name, a comma, and the Request Date. I made a rule to do this. Example of what I want: “Partner A, 8/27/2010”.  However, the result is that the Partner ID is used instead of the Partner Name and what I get is “1, 8/27/2010”.   (More: In production I would use the Form Submit rule to do this. For development I used a rule that runs when the field changes. If I change the Drop-Down List Box properties Value property (on the data tab) from d:ID to d:Title the string is formatted as desired, but the form won’t submit and shows a tooltip that only positive integers are allowed.)   How can I make the rule use the Partner Name column in the formula for the rule, instead of the ID?   Thank you, James.

A tidy and efficient way of excluding rows with the same column value or equal to null


Good morning

I frequently use the pivot statement to generate columns I wish to compare, often looking for differences between the values in the columns. Quite often the pivot returns some null values which I am having some difficulty in comparing.


Rownum              Col1       col2        col3    col4  etc

1                              A             A             A     &

datediff with criteria in a column


dear all,

i have a table with columns serial_no, item_no, cost,receipt date. i need to write an qeury that looks like the following:

select item_no, cost, datediff(d,receiptdate,getdate())<=30 as "30",datediff(d,receiptdate,getdate())between 31 and 60 as "60",

datediff(d,receiptdate,getdate())between 61 and 90 as "90"

from table.

i cannot do this as mentioned above, i get Incorrect syntax near '<'.

how can i accomplish the above on. it is basically that i want to sum the items and cost  that was received less and 30 days ago, than column "60" = receipt date is between 30 and 60 days old, and column 90 means receipt date of items that are bewteen 61 and 90 days.

I will really appreciate any help.....

Calculate Ageing using Formula Column



What I'm after is to create an ageing column that displays the number of days that a document has been in our library.  We've eliminated the whole calulated column thing because it's not dynamic. Where we've arrived now is to create a List View and are using SharePoint designer to create a calculated column. We get to the point where we open up the XPath expression builder an we're trying different syntax, but can't seem to get it figured out.  We have a column that exists called DateDumped that we just want to know the number of days since that date based on todays date. Any suggestion on the syntax or any other ideas would be greatle appreciated.

Thanks in advance.


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.

Workaround to using "Person or Group" field in a "Calculated" column formula?


Apparently, SharePoint does not allow you to insert a "Person or Group" column into a "Calculated" field's formula.

Is there a workaround to adding a "Person or Group" column into a "Calculated" field's formula?

-Maybe a way of coverting the data from the Person or Group column into a text field, and referencing that text field in the formula?

Microsoft mentions only Columns of the following data types can be referenced in a formula: single line of text, number, currency, date and time, choice, yes/no, and calculated.

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?


Declaring a foreign key to a column of 'formula' datatype

I have a primary key declared as 'Computed'. Trying to relate a foreign key to this column in the Database Diagram, it complains on data types being different. Declaring the Foreign key as nvarchar or varchar did not help. When declaring it as 'Computed', column was not available for selection as a foreign key. Any advice?
Baruch Barness

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

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