Extensive if statement with multiple if formula issue

Question

The below column formula returns with syntax error. I need to capture if there is no closed date, calculate amount of days open from the Escalated Date, or calculate amount of days from Escalated Date with Case Closed Date.

Each true and false formula individually works; however, it does not work when it's led with IF(ISBLANK([CASE CLOSED DATE],...

HELP!!

IF(ISBLANK([CASE CLOSED DATE],

IF(ISERROR(DATEDIF([ESCALATED DATE],TODAY(),"d")),"",(DATEDIF([ESCALATED DATE],TODAY(),"d"))-INT(DATEDIF([ESCALATED DATE],TODAY(),"d")/7)*2-IF((WEEKDAY(TODAY())-WEEKDAY([ESCALATED DATE]))<0,2,0)-IF(OR(AND(WEEKDAY(TODAY())=7,WEEKDAY([ESCALATED DATE])=7),AND(WEEKDAY(TODAY())=1,WEEKDAY([ESCALATED DATE])=1)),1,0)-IF(AND(WEEKDAY([ESCALATED DATE])=1,(WEEKDAY(TODAY())-WEEKDAY([ESCALATED DATE]))>0),1,0)-IF(AND(NOT(WEEKDAY([ESCALATED DATE])=7),WEEKDAY(TODAY())=7),1,0)+IF(HOUR(TODAY())<HOUR([ESCALATED DATE]),0,IF(TEXT(TODAY()-[ESCALATED DATE],"h")>=4,0.5,1)))
,

IF(ISERROR(DATEDIF([ESCALATED DATE],[Case Closed Date],"d")),"",(DATEDIF([ESCALATED DATE],[Case Closed Date],"d"))-INT(DATEDIF([ESCALATED DATE],[Case Closed Date],"d")/7)*2-IF((WEEKDAY([Case Closed Date])-WEEKDAY([ESCALATED DATE]))<0,2,0)-IF(OR(AND(WEEKDAY([Case Closed Date])=7,WEEKDAY([ESCALATED DATE])=7),AND(WEEKDAY([Case Closed Date])=1,WEEKDAY([ESCALATED DATE])=1)),1,0)-IF(AND(WEEKDAY([ESCALATED DATE])=1,(WEEKDAY([Case Closed Date])-WEEKDAY([ESCALATED DATE]))>0),1,0)-IF(AND(NOT(WEEKDAY([ESCALATED DATE])=7),WEEKDAY([Case Closed Date])=7),1,0)+IF(HOUR([Case Closed Date])<HOUR([ESCALATED DATE]),0,IF(TEXT([Case Closed Date]-[ESCALATED DATE],"h")>=4,0.5,1)))
)

Ganesh Sanap - MVP · Accepted Answer · 2025-03-28 12:50:20Z

Closing parenthesis for ISBLANK function is missing in your formula, near IF(ISBLANK([CASE CLOSED DATE],. It should be like:

IF(ISBLANK([CASE CLOSED DATE]),

Try using this formula:

IF(ISBLANK([CASE CLOSED DATE]),IF(ISERROR(DATEDIF([ESCALATED DATE],TODAY(),"d")),"",(DATEDIF([ESCALATED DATE],TODAY(),"d"))-INT(DATEDIF([ESCALATED DATE],TODAY(),"d")/7)*2-IF((WEEKDAY(TODAY())-WEEKDAY([ESCALATED DATE]))<0,2,0)-IF(OR(AND(WEEKDAY(TODAY())=7,WEEKDAY([ESCALATED DATE])=7),AND(WEEKDAY(TODAY())=1,WEEKDAY([ESCALATED DATE])=1)),1,0)-IF(AND(WEEKDAY([ESCALATED DATE])=1,(WEEKDAY(TODAY())-WEEKDAY([ESCALATED DATE]))>0),1,0)-IF(AND(NOT(WEEKDAY([ESCALATED DATE])=7),WEEKDAY(TODAY())=7),1,0)+IF(HOUR(TODAY())<HOUR([ESCALATED DATE]),0,IF(TEXT(TODAY()-[ESCALATED DATE],"h")>=4,0.5,1))),IF(ISERROR(DATEDIF([ESCALATED DATE],[Case Closed Date],"d")),"",(DATEDIF([ESCALATED DATE],[Case Closed Date],"d"))-INT(DATEDIF([ESCALATED DATE],[Case Closed Date],"d")/7)*2-IF((WEEKDAY([Case Closed Date])-WEEKDAY([ESCALATED DATE]))<0,2,0)-IF(OR(AND(WEEKDAY([Case Closed Date])=7,WEEKDAY([ESCALATED DATE])=7),AND(WEEKDAY([Case Closed Date])=1,WEEKDAY([ESCALATED DATE])=1)),1,0)-IF(AND(WEEKDAY([ESCALATED DATE])=1,(WEEKDAY([Case Closed Date])-WEEKDAY([ESCALATED DATE]))>0),1,0)-IF(AND(NOT(WEEKDAY([ESCALATED DATE])=7),WEEKDAY([Case Closed Date])=7),1,0)+IF(HOUR([Case Closed Date])<HOUR([ESCALATED DATE]),0,IF(TEXT([Case Closed Date]-[ESCALATED DATE],"h")>=4,0.5,1))))

Note:

Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
Use correct display name of your SharePoint columns in above formula.
Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Stack Exchange Network

Extensive if statement with multiple if formula issue

1 Answer 1

Your Answer

Hot Network Questions

Extensive if statement with multiple if formula issue

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions