Calculated Column Formula Syntax Error

Question

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.

Answer 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!!!)

Answer 2

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