1

I am trying to setup a list that I can use to calculate deadlines for an approval process. I was asked to set the deadline to 5 working days, and exclude the 2 weekend days. I found a really helpful article, and was attempting to adjust their formula to work with my needs.

However when I made the adjustments, SharePoint threw a syntax error. Here are the formulas:

Original:

=IF(OR(WEEKDAY(CurrentDate)=1,WEEKDAY(CurrentDate)=7),CurrentDate+3,IF(OR(WEEKDAY(CurrentDate)=5,WEEKDAY(CurrentDate)=6),CurrentDate+4,IF(OR(WEEKDAY(CurrentDate)>1,WEEKDAY(CurrentDate)<4),CurrentDate+2))) 

Adjusted:

=IF(WEEKDAY(CurrentDate)=1),CurrentDate+5,IF(WEEKDAY(CurrentDate)=7),CurrentDate+6,IF(OR(WEEKDAY(CurrentDate)>1,WEEKDAY(CurrentDate)<6,CurrentDate+7))

I am assuming I am making a simple mistake (fledgling SP Admin), but could use some expert advice.

1

Missing parens...

Try this:

=IF(WEEKDAY(CurrentDate)=1,CurrentDate+5,IF(WEEKDAY(CurrentDate)=7,CurrentDate+6,IF(OR(WEEKDAY(CurrentDate)>1,WEEKDAY(CurrentDate)<6),CurrentDate+7)))

Here's a shorter version that will probably do what you need:

= CurrentDate+5 + IF(WEEKDAY(CurrentDate+5)=1,1,0) + IF(WEEKDAY(CurrentDate+5)=7,2,0)

(No nested IFs!!!)

  • No syntax error, but doesn't handle fridays correctly – Per Jakobsen Mar 15 '18 at 19:45
  • Ah... you asked about the syntax error... I'll add an alternate solution to the above. – Mike Smith - MCT - MVP Mar 15 '18 at 20:16
0

You have several small problems:

  • Extra close parenthesis after condition in first two IF's
  • The last IF has several issues:
    • a close parenthesis for the OR
    • now you have three nested if so you need a close parenthesis more at the end
    • What should happen if WEEKDAY(CurrentDate) = 6?

The best way to play with a formular like this is to use Excel:

  1. Create a new notebook
  2. Enter a date in A1
  3. Label A1 to be CurrendDate
  4. Now you can play with the formular in A2 (or any other cell)

My suggestion:

=IF(WEEKDAY(CurrentDate)=1,CurrentDate+5,IF(WEEKDAY(CurrentDate)=7,CurrentDate+6,CurrentDate+7))

or if you can assume that items are only created on working days

=CurrentDate+7

  • =CurrentDate+7 ... sometimes it is the simple answers. Have a ways to go gents, thanks for the help. – Gwall Mar 16 '18 at 15:23

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.