question

KristenThompson-4041 avatar image
0 Votes"
KristenThompson-4041 asked EchoDu-MSFT commented

How do I convert an Excel formula to Sharepoint Calculated Field?

I have a couple of Excel formula's that I need to add into my SharePoint Lists. Below are the formula's as they are in Excel. I just need to know how I can change them to the appropriate syntax that will allow for a SharePoint List calculated column.

  1. =IF([@[Actual Completion Date]]="",NETWORKDAYS([@[Date Prioritised]],TODAY()),NETWORKDAYS([@[Date Prioritised]],[@[Actual Completion Date]]))

  2. See Formula attached


[1]: /answers/storage/attachments/87368-image.png

office-sharepoint-online
image.png (76 B)
image.png (52.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @KristenThompson-4041 ,

This is a quick note to let you know that we are performing research on this issue.

Thanks,
Echo Du

0 Votes 0 ·

1 Answer

EchoDu-MSFT avatar image
0 Votes"
EchoDu-MSFT answered EchoDu-MSFT commented

Hi @KristenThompson-4041 ,

The first formula:

 =ROUNDUP((IF(ISBLANK([Actual Completion Date]),DATEDIF([Date Prioritised],NOW(),"D")-(ROUND((DATEDIF([Date Prioritised],NOW(),"D"))/7,0)*2),DATEDIF([Date Prioritised],[Actual Completion Date],"D")-(ROUND((DATEDIF([Date Prioritised],[Actual Completion Date],"D"))/7,0)*2))),0)

87946-1.png

87959-2.png

There is no corresponding formula in SharePoint to lookup field values across another list. Therefore, the second Excel formula cannot be implemented in SharePoint Calculated columns.

Thanks,
Echo Du
=====================
If an Answer is helpful, please click "Accept Answer" and upvote it.
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.





1.png (97.2 KiB)
2.png (14.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KristenThompson-4041 ,

Would you tell me whether your issue has been resolved or have any update?
I am looking forward to your reply.

Have a nice day!

Thanks,
Echo Du

0 Votes 0 ·

Thank you so much!

This worked great.
Do you have any other resources on how I can use certain EXCEL formulas and turn them into Sharepoint versions?

0 Votes 0 ·
EchoDu-MSFT avatar image EchoDu-MSFT KristenThompson-4041 ·

Hi @KristenThompson-4041 ,

Thanks for your reply.

You can see the following resource links, which may help you.

Have a nice day!

Thanks,
Echo Du





0 Votes 0 ·
EchoDu-MSFT avatar image EchoDu-MSFT KristenThompson-4041 ·

Hi @KristenThompson-4041 ,

Is there anything else I can help with regarding this issue?
You can comment us at any time and we will continue to follow up.

Thanks,
Echo Du

0 Votes 0 ·
EchoDu-MSFT avatar image EchoDu-MSFT KristenThompson-4041 ·

Hi @KristenThompson-4041 ,

Would you tell me whether your issue has been resolved or have any update?
I am looking forward to your reply.

Have a nice day!

Thanks,
Echo Du

0 Votes 0 ·