SharePoint column default values – add 10 working days


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:

=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 Solution

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 Question