2

Basically, I'm trying to use SharePoint as some what of a project tool for tasks.

On a new task, we have "START DATE", and a custom number field "ETC". I'd like to have a calculated field "END DATE" populated by adding the ETC (estimated time to complete) to the START DATE, but the result should display a date that is within a business work week (Monday through Friday).

i.e.

Start Date = 4/8/2015

ETC = 8 Days

End Date (excluding weekends) = 4/20/2015  (Calculated Field)
1

1 Answer 1

2
=[START DATE]+[ETC] 
+ ROUNDDOWN([ETC]/5,0)*2 
+ IF(WEEKDAY([START DATE])+MOD([ETC],5)>=7,2,0)
- ROUNDDOWN(WEEKDAY([START DATE])/7,0) 
+ IF(AND(MOD([ETC],5)=0,WEEKDAY([START DATE])=1),-2,0)  
+ IF(AND(MOD([ETC],5)=0,WEEKDAY([START DATE])=7),-2,0) 

Here's where I copied this from.

2
  • Perfect, wow, I was starting to think I couldn't do this. Thank you! Apr 10, 2015 at 18:11
  • Does this work in infopath?
    – HRY
    May 19, 2020 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.