This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(166.4, 40%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECC%3C/text%3E%3C/svg%3E)
Which is the formula to microsoft lists from excel:
excel : =IF(AND(ISNUMBER([@[Expected Approval for
SM]]);[@[IN / SM]]="SM");NETWORKDAYS.INTL([@[Expected Approval for
SM]];[@[Actual
Approval Date]];1)-1;IF(AND(ISNUMBER([@[Expected Approval for
Initial]]);[@[IN / SM]]="IN");NETWORKDAYS.INTL([@[Expected Approval for
Initial]];[@[Actual
Approval Date]];1)-1;""))
lists formula generated from AI and does not work: =IF(AND(ISNUMBER([Expected Approval for SM]);[IN / SM]="SM");NETWORKDAYS.INTL([Expected Approval for SM];[Actual Approval Date];1)-1;IF(AND(ISNUMBER([Expected Approval for Initial]);[IN / SM]="IN");NETWORKDAYS.INTL([Expected Approval for Initial];[Actual Approval Date];1)-1;""))
May I have your help please?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(313.6, 51%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EED%3C/text%3E%3C/svg%3E)
Yes, you could combine these two formulas in one.
In the Expected Approval calculated column,
=IF([Submission Type]="Initial";IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+128;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+42;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+60;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+80;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+111;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+25;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+43;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+63;""))))))));IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+109;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+74;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+90;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+93;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+92;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+57;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+73;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+76;"")))))))))
In the Days calculated column,
=IF([Expected Approval]<[Actual Approval Date];DATEDIF([Expected Approval];[Actual Approval Date];"d")-INT(DATEDIF([Expected Approval];[Actual Approval Date];"d")/7)*2-IF(WEEKDAY([Actual Approval Date])<WEEKDAY([Expected Approval]);2;IF(OR(WEEKDAY([Actual Approval Date])=7;WEEKDAY([Expected Approval])=1);1;0));IF([Expected Approval]>[Actual Approval Date];-(DATEDIF([Actual Approval Date];[Expected Approval];"d")-INT(DATEDIF([Actual Approval Date];[Expected Approval];"d")/7)*2-IF(WEEKDAY([Expected Approval])<WEEKDAY([Actual Approval Date]);2;IF(OR(WEEKDAY([Expected Approval])=7;WEEKDAY([Actual Approval Date])=1);1;0)))))
Result:
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Emily Thank you very much for your help !!! Everything is working right now!! Really happy for it!!!
You could use following formula in the SharePoint calculated column.
=IF(AND(LEN([Expected Approval for SM])>0,[IN / SM]="SM"),DATEDIF([Expected Approval for SM],[Actual Approval Date],"d")-INT(DATEDIF([Expected Approval for SM],[Actual Approval Date],"d")/7)*2-IF(WEEKDAY([Actual Approval Date])<WEEKDAY([Expected Approval for SM]),2,IF(OR(WEEKDAY([Actual Approval Date])=7,WEEKDAY([Expected Approval for SM])=1),1,0)),IF(AND(LEN([Expected Approval for Initial])>0,[IN / SM]="IN"),DATEDIF([Expected Approval for Initial],[Actual Approval Date],"d")-INT(DATEDIF([Expected Approval for Initial],[Actual Approval Date],"d")/7)*2-IF(WEEKDAY([Actual Approval Date])<WEEKDAY([Expected Approval for Initial]),2,IF(OR(WEEKDAY([Actual Approval Date])=7,WEEKDAY([Expected Approval for Initial])=1),1,0))))
Result:
*************************************************************************
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Screenshot 2025-02-25 130156.jpg
Dear Emily
First of all thank you very much for your help
Unfortunately formula does not work in my case cause I suppose there are more values that need to be considered and not mentioned.
May you please share your email with me in order to give u access to my list to figure it out?
Columns [Expected Approval for SM] and [Expected Approval for Initial] are already calculated columns, I am just verifying if it is necessary
The forum's privacy rules do not allow email addresses to be provided.
I suggest you provide the calculated formula for Columns [Expected Approval for SM] and [Expected Approval for Initial]. Then I can test in my end.
No problem at all:
[Expected Approval for Initial]
=IF([Submission Type]="Initial";IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+128;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+42;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+60;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+80;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+111;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+25;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+43;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+63;"")))))))))
[Expected Approval for SM]
=IF([Submission Type]="SM";IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+109;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+74;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+90;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+93;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+92;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+57;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+73;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+76;"")))))))))
Formula that is working but includes weekends is:
=IF(ISNUMBER([Expected Approval for SM]);[Actual Approval Date]-[Expected Approval for SM];IF(ISNUMBER([Expected Approval for Initial]);[Actual Approval Date]-[Expected Approval for Initial];))
and need to exclude weekends from it
I notice ISNUMBER([Expected Approval for SM]) in the formula, whether you want to check column [Expected Approval for SM] is blank or other purpose?
It will never be blank cause it is a required section. It would be either initial or SM
It will never be blank cause it is a required field. It would be either Initial or SM
Thanks for your reply.
Since I'm almost off work, I'll do the test during work hours tomorrow and let you know the result.
You could use following formula in the SharePoint calculated column.
=IF([Submission Type]="SM";DATEDIF([Expected Approval for SM];[Actual Approval Date];"d")-INT(DATEDIF([Expected Approval for SM];[Actual Approval Date];"d")/7)*2-IF(WEEKDAY([Actual Approval Date])<WEEKDAY([Expected Approval for SM]);2;IF(OR(WEEKDAY([Actual Approval Date])=7;WEEKDAY([Expected Approval for SM])=1);1;0));IF([Submission Type]="Initial";DATEDIF([Expected Approval for Initial];[Actual Approval Date];"d")-INT(DATEDIF([Expected Approval for Initial];[Actual Approval Date];"d")/7)*2-IF(WEEKDAY([Actual Approval Date])<WEEKDAY([Expected Approval for Initial]);2;IF(OR(WEEKDAY([Actual Approval Date])=7;WEEKDAY([Expected Approval for Initial])=1);1;0))))
Result:
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Screenshot 2025-02-27 150638.jpg
Thank you very much Emily, formula is working right but does not give a value when the actual approval date is earlier that the expected approval for Initial or SM. Is there any case to fix this?
And one more question please,
Can we combine these two formulas in one:
[Expected Approval for Initial]
=IF([Submission Type]="Initial";IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+128;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+42;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+60;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+80;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+111;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+25;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+43;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+63;"")))))))))
[Expected Approval for SM]
=IF([Submission Type]="SM";IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+109;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+74;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="Yes");[Submission date]+90;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="Yes");[Submission date]+93;IF(AND([RFI-V]="Yes";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+92;IF(AND([RFI-V]="No";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+57;IF(AND([RFI-V]="Yes";[RFI-A]="No";[Winter Clock Stop]="No");[Submission date]+73;IF(AND([RFI-V]="No";[RFI-A]="Yes";[Winter Clock Stop]="No");[Submission date]+76;"")))))))))
and have then in the same column as Expected Approval?