Share via

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

Kristen Thompson 1 Reputation point
Apr 13, 2021, 7:31 PM

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
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,068 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Echo Du_MSFT 17,216 Reputation points
    Apr 15, 2021, 1:21 AM

    Hi @Kristen Thompson ,

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.