SharePoint column default values - add 10 working days

Question

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.

Answer 1

Perhaps you can work around this limitation by using a workflow (possibly a custom one) to manage the due date? A due date implies that it is an actionable item that should be assigned to somebody anyways.

Note that VS.NET doesn't have to be a luxury - you can use it for free.

Answer 2

Firstly I should point out that you are making hard work of that formula, this will do the same.

=[Incoming Date] + 10

From the comments you have figured out that 10 working days (M-F) will always have 2 weekends so you can use this

=[Incoming Date] + 14

But this still doesn't take account of holidays

You are not going to be able to do this without some custom code in a workflow or possibly some javascript 'hack' and a database of holiday days for your region.

One possibility would be to default your Due Date to 10 working days from now when the record is created

=Today+14

and then rely on your users to manual alter this date if there are holidays in that period.

More details on this in a blog entry I've just written - Working Days, Weekends and Holidays in SharePoint Calculated Columns

Answer 3

I believe I've figured out a fairly bullet-proof method for calculating a 10 business day deadline that accounts for holidays and weekends. 1) Calculate whether the 2 week period is a Monday, and if so, add only 11 days (assuming the start day counts as Day1 of your 10-day period). Otherwise, you add 13 to account for the 10 working days plus two weekends (remember, the start date already counts as Day1; your variables would be 12 and 14 if you did NOT count the start date as Day1). 2) Create a unique calculated column for every holiday and return a value of 1 if the holiday falls in the range. 3) Determine your "gross date" by adding values (weekends and holidays) to your start date. 4) Determine whether your gross date falls on a Saturday or Sunday, and if so, return the appropriate number of days to push off until Monday. 5) Add all the weekend, holiday, and added Sat and Sun values to your start date, which gives you your due date.

NOTE: The only challenges I see here is if a holiday pushes the due date into the weekend, which then pushes the due date to a Monday that happens to be yet another holiday. This didn't happen in my holiday schedule, but it might in yours. In addition, you'll need to keep adding new holidays every year, thus requiring you to recreate the column arrays from scratch for a long-running list. Alternatively, you could start a new list every year.

C_Wknd  =IF(TEXT(WEEKDAY([Complaint Created On]),"ddd")="Mon",11,13)

C_NYDay =IF(AND([Complaint Created On]<=DATE(2009,1,1),([Complaint Created On])+C_Wknd>=DATE(2009,1,1)),"1","0")

C_MLKDay    =IF(AND([Complaint Created On]<=DATE(2009,1,19),([Complaint Created On])+C_Wknd>=DATE(2009,1,19)),"1","0")

C_MemDay    =IF(AND([Complaint Created On]<=DATE(2009,5,25),([Complaint Created On])+C_Wknd>=DATE(2009,5,25)),"1","0")

C_PresDay   =IF(AND([Complaint Created On]<=DATE(2009,2,16),([Complaint Created On])+C_Wknd>=DATE(2009,2,16)),"1","0")

C_IndDay    =IF(AND([Complaint Created On]<=DATE(2009,7,4),([Complaint Created On])+C_Wknd>=DATE(2009,7,4)),"1","0")

C_LabDay    =IF(AND([Complaint Created On]<=DATE(2009,9,7),([Complaint Created On])+C_Wknd>=DATE(2009,9,7)),"1","0")

C_ColDay    =IF(AND([Complaint Created On]<=DATE(2009,10,12),([Complaint Created On])+C_Wknd>=DATE(2009,10,12)),"1","0")

C_VetDay    =IF(AND([Complaint Created On]<=DATE(2009,11,11),([Complaint Created On])+C_Wknd>=DATE(2009,11,11)),"1","0")

C_ThxDay    =IF(AND([Complaint Created On]<=DATE(2009,11,26),([Complaint Created On])+C_Wknd>=DATE(2009,11,26)),"1","0")

C_XmsDay    =IF(AND([Complaint Created On]<=DATE(2009,12,25),([Complaint Created On])+C_Wknd>=DATE(2009,12,25)),"1","0")

C_GrossDte  =[Complaint Created On]+C_Wknd+C_NYDay+C_MLKDay+C_MemDay+C_PresDay+C_IndDay+C_LabDay+C_ColDay+C_VetDay+C_ThxDay+C_XmsDay

C_EndSat    =IF(TEXT(WEEKDAY(C_GrossDte),"ddd")="Sat",2,0)

C_EndSun    =IF(TEXT(WEEKDAY(C_GrossDte),"ddd")="Sun",1,0)

Resolution Due  =C_GrossDte+C_EndSat+C_EndSun