Friday, February 8, 2013

Calculated Column - Add x days to created date, excluding weekends.


Overview

Say a user creates a item in a list and they have 3 (working days) to to mark it as completed, how do we do this?

1. Create a new field in the list (calculated field of type date and time), lets call it "escalate date", then using this formula.

=IF(Weekday([Modified])>3, [Modified]+5, IF(Weekday([Modified])>1,[Modified]+3, [Modified]+4))

2. Create a workflow that starts on item created and changed, that pauses until the "escalate date", once that date is reached, workflow checks if item was marked as completed or not.


Explanation of Weekday.

Using Weekday, we can determine which day of the week the share point list item was created and add 3 working days to a calculated column.


WeekdayReturnsDays to addEscalate Date
Sunday1+4Thursday
Monday2+3Thursday
Tuesday3+3Friday
Wednesday4+5Monday
Thursday5+5Tuesday
Friday6+5Wednesday
Saturday7+5Thursday

4 comments:

  1. Hi I need to do for 2 working days, Can you explain the formula.

    ReplyDelete
  2. Hi,

    for 2 days, you need to modify the formula. You can easily work it out using excel. I did a quick formula change for you. This should work for SharePoint.

    =IF(WEEKDAY([YOURDATEFIELD])>=5;IF(WEEKDAY([YOURDATEFIELD])=7;[YOURDATEFIELD]+3;[YOURDATEFIELD]+4);IF(WEEKDAY([YOURDATEFIELD])>1;[YOURDATEFIELD]+2;[YOURDATEFIELD]+2))

    ReplyDelete
  3. Hi, The above formula works perfectly, Thank you so much for sharing your knowledge.

    ReplyDelete