SharePoint column default values – add 10 working days

mosssharepoint

In SharePoint MOSS 2007, I have created a custom content type that I will be applying to a document library. One of the required fields is "Incoming Date" and another is the "Due Date".

The Due Date is always 10 working days from the Incoming Date. The Incoming Date is when the mail room received the letter, not necessarily when the document is posted to the library.

From here: http://msdn.microsoft.com/en-us/library/bb862071.aspx

=DATE(YEAR([Incoming Date]),MONTH([Incoming Date]),DAY([Incoming Date])+10)

adds 10 days, but how can I add 10 working days? I don't have the luxury of VS.NET either per the governance plan of our sharepoint rollout.

Assume a human is responsible for the data entry, but I would like to make it easier for them.

Best Answer

It's overkill for the very specific '10 days' requirement, but this should calculate a due date for any number of days from any start date.

I wrote it to match the result of Excel's WORKDAY function (which, given that every function in a calculated field is an Excel function, should almost be a thing). It's tested for 1 to 146 "days to complete", for each day of the week, and across years, without any sign of inconsistency. Unless I made a typo copying it from Excel, it should work as advertised. The only down side is that it doesnt do holidays, but if the users are accustomed to SharePoint they wont have expectations anyway. At all. Of any kind. For anything. Or hope. Or the muscles in their face that used to be responsible for smiling. Or the ability to look at a child and see anything but the bleak certainty of withering death. So, not a big deal if their task is due on Christmas. It's also sloppier than it probably needs to be.

=[Start Date]+[Days to Complete] 
+ ROUNDDOWN([Days to Complete]/5,0)*2 
+ IF(WEEKDAY([Start Date])+MOD([Days to Complete],5)>=7,2,0)
- ROUNDDOWN(WEEKDAY([Start Date])/7,0) 
+ IF(AND(MOD([Days to Complete],5)=0,WEEKDAY([Start Date])=1),-2,0)  
+ IF(AND(MOD([Days to Complete],5)=0,WEEKDAY([Start Date])=7),-2,0)

The first line is...obvious. The second line adds weekends. And the next 4 lines adjust for the deficiencies of the second line.

Related Solutions

R – Writing Database Blobs to SharePoint Document Libaray

You can easily create document programmatically from a byte array. Here's a bit of code snippet that you can build upon.

// If you have very large document, you might consider export the BLOBs to disk instead of in memory
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]“, conn);
DataSet ds= new DataSet();
da.Fill(ds);

byte[] blob = (byte[])ds.Tables[0].Rows[0]["imageContent"];


using (SPSite destinationSite = new SPSite("http://YourSite"))
{
  using (SPWeb = destinationSite.OpenWeb("http://YourSite"))
  {
    SPList destinationDocuments = destinationWeb.Lists["Documents"];
    string fileName = "mydoc.jpg";
    string relativeDestinationUrl = destinationDocuments.RootFolder.Url + "/";
    stringfileName += sourceItem.File.Name;
    relativeDestinationUrl += fileName;

    SPFile destinationFile = ((SPDocumentLibrary)destinationDocuments).RootFolder.Files.Add(relativeDestinationUrl, blob, true);
    SPListItem item = destinationFile.Item;
    item.UpdateOverwriteVersion();
  }
}

R – Workflow initiated by difference in Modified and Current dates – SharePoint

You could try using the "Pause for duration" action (under "Core Actions" when you are adding an action to your SPD workflow).

However, I've worked on something that had a similar requirement and we decided that workflow was not the best option. We have a console application that is scheduled to run everyday and queries the list for "old" items. If it finds any old items, then it sends out an email to the user.

Best Answer

Related Solutions

R – Writing Database Blobs to SharePoint Document Libaray

R – Workflow initiated by difference in Modified and Current dates – SharePoint

Related Topic