Forum Discussion
Solved
Need help with syntax error
Hello! I am having issues with a nested IF formula, with ISBLANK and WORKDAY functions in a calculated column. I have tested the formula in Excel and it worked fine. I have tried changing all the commas to semicolons, but keep receiving a syntax error.
=IF(ISBLANK([Submission Date]),"No Submit Date",IF(ISBLANK([Completed Date]),"No Completed Date",IF(WORKDAY([Submission Date],2)>[Completed Date],"Met","Missed")))
Any help is appreciated!
KCarp0113 you're getting the syntax error because the WORKDAY function doesn't exist in SharePoint, it is only for Excel.
For part of your fomula it looks as though you want to get the number of working days between 2 dates. So you need to be using a combination of DATEDIF AND WEEKDAY (where Start and End are my date/time columns):
=(DATEDIF(Start,End,"D"))-INT(DATEDIF(Start,End,"D")/7)*2-IF(WEEKDAY(End)<WEEKDAY(Start),2,IF(OR(WEEKDAY(End)=7,WEEKDAY(Start)=1),1,0))Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
2 Replies
KCarp0113 you're getting the syntax error because the WORKDAY function doesn't exist in SharePoint, it is only for Excel.
For part of your fomula it looks as though you want to get the number of working days between 2 dates. So you need to be using a combination of DATEDIF AND WEEKDAY (where Start and End are my date/time columns):
=(DATEDIF(Start,End,"D"))-INT(DATEDIF(Start,End,"D")/7)*2-IF(WEEKDAY(End)<WEEKDAY(Start),2,IF(OR(WEEKDAY(End)=7,WEEKDAY(Start)=1),1,0))Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)RobElliott Thank you for your help! I was able to modify your formula a bit and combine it with my original to get the results I wanted. I sometimes forget which functions don't crossover. Thanks again!
