0

I'm looking for some help. I'm trying to setup a calculated field to determine a due date of a record.

The due date needs to exclude weekends, and also is dependent on two addition columns. Organisation and Severity

For example:

  1. Organisation N has severity of standard (12 hours) and priority (48 hours)
  2. Organisation T has severity of Standard (72 hours) and priority (24 hours)

If the due date lands on a Saturday - then I have to plus 2 days to the due date, if it lands on Sunday, then I plus 1 etc.

I've tried the below, but I'm not getting any return on this, and I need to replicate it 3 more times to meet all 4 criteria. Any help appreciated.

=IF(OR([Organisation Clear]="T",Severity="Standard"),(AND(WEEKDAY([Case Start Date]+3)=1,[Case Start Date]+4,([Case Start Date]+3)=7,[Case Start Date]+5,[Case Start Date]+3)))

1 Answer 1

0

Assuming two columns named [Start Date] and [Working Days] you can calculate the weekday date using:

=[Start Date] + [Working Days]
 + IF( WEEKDAY( [Start Date], 3) < 5,
       1 + FLOOR( ( [Working Days] - 6 + WEEKDAY( [Start Date], 3) ) / 5, 1 ) * 2,
       7 - WEEKDAY( [Start Date], 2 ) + FLOOR( ( [Working Days] - 1) / 5, 1 ) * 2
     )

The details of how this works is in my SharePoint Calculated Columns and Validatoin Formulas book. :-)

[Working Days] is a number, so 12 hours would be 0.5.

To expand this to what you need, replace each occurrence of "[Working Days]" with your calculation for days.

i.e. replace "[Working Days]" with: (don't type the comments :-) )

IF( [Organisation]="N",                     -- if N
     IF( [Severity] = "Standard", 0.5, 2 ), --   then check severity and return time
     IF( [Organisation]="T",                --   else if T
       IF( [Severity] = "Standard", 3, 1 )  --     then check severity and return time
     ),                                     --     end of the inner IF
     0                                      --   none of the above, return 0
  )                                         -- end of the outer IF

To simplify the main formula, put the above in its own column named [Working Days] and just don't include it in the view.

 

By the way, is this correct? "Organisation N has severity of standard (12 hours) and priority (48 hours)" "Priority" status is longer than "Standard"?

3
  • Hi Mike. Firstly, thank you so much for spending time to reply, its appreciated. I've got the Working days to work as expected, thank you for that. In terms of the final formula, anything that was created yesterday (Monday) returns a value of 5, everything else gets the correct Date. Could this be something to do with regional as I'm UK?
    – Dave jones
    Jul 23, 2019 at 11:02
  • =Created+[Working Days]+IF(WEEKDAY(Created,3)<5,1+FLOOR(([Working Days]-6+WEEKDAY(Created,3))/5,1)*2,7-WEEKDAY(Created,2)+FLOOR(([Working Days]-1)/5,1)*2)
    – Dave jones
    Jul 23, 2019 at 11:02
  • I've just tried to put this formula into excel too, 1 day from created results in the same day, it doesn't seem to +1 the date?
    – Dave jones
    Jul 23, 2019 at 11:50

Your Answer

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

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