1

I am trying to assign a due date based on the outcome of a column.

I have a drop down on a list with the choice Complaint or No Complaint called Related to Complaint?

I am trying to set a due date based on the outcome of the following logic:

if related to complaint? = Complaint then 
    due date = created date + 2 working days

if related to complaint? = No Complaint then 
    due date = created date + 10 working days.

I've been struggling to find a way of doing this and I am new to using SharePoint. Any assistance would be greatly appreciated.

Thanks

0

I made a formula that should work for you.

Since you're adding 10 working days to the second option, that's the same as adding two regular weeks, assuming your work days are Monday to Friday. So that's easy, it's just if (No Compliant) then [DueDate] = [Created]+2

But with the second option we're adding two days, which isn't any sort of easy multiple of a week. So if you look at a calendar that starts on Sunday, adding two days to Sunday, Monday, Tuesday, or Wednesday is fine. They'll still land on a week day.

If you're adding two to Thursday or Friday we need to add two more days to skip the weekend to land back on Monday or Tuesday.

If we're adding two to Saturday, we need to add one more day to the column to make up for Sunday.

So following that logic I came up with this:

=IF(
   [Related to Compliant]="Yes",
   IF(
      OR(
         WEEKDAY(Created)=5,
         WEEKDAY(Created)=6
      ),
      Created+4,
      IF(
         WEEKDAY(Created)=7,
         Created+3,
         Created+2
      )
   ),
   Created+14
)

I changed the values of [Related to Compliant] to simply "Yes" or "No" for simplicity sake. If you end up having more options later that have different calculations to Due Date then replace the simple Created+14 with IF([Related to Compliant]="No",Created+14,"") and replace the double quotes with more logic if you need to. But for now, this should work.

Should be able to just copy and paste the above block into your Due Date calculated column. No need to remove the spaces, as SharePoint will do that for you when you hit save.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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