Hi guys, I need some help with the formula to create a calculated columns in sharepoint

Anonymous
2022-04-06T11:37:34.877+00:00

Hi guys, I need some help with the formula to create a calculated columns in sharepoint.

Let say, we have Contract Execution Date (date and time) column and Potential TCV (Currency) column, i want to calculate amount in Spread form (Jan, Feb, Mar... Dec)

Example: Account A has Potential TCV is $1000 and Contract Execution Date is 02/27/2022, then $1000 should show me in Feb column (that i want to calculate), rest months should remain empty.

Similarly, if Account B has Potential TCV is $1500 and Contract Execution Date is 06/21/2022, then $1500 should show me in Feb column (that i want to calculate), rest months should remain empty.

Thanks

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,297 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,807 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yi Lu_MSFT 17,471 Reputation points
    2022-04-07T02:18:25.96+00:00

    Hi @Anonymous
    You need to create 12 calculated columns for mons(Jan, Feb,,,Dec).

    You could use below formula for Jan, data type choose currency:

    =IF(MONTH([Contract Execution Date])=1,[Potential TCV],"0")  
    

    190726-image.png
    Then create the rest 11 calculated columns, just replace "1" to "2" when create Feb, replace "1" to "3" when create March and so on.

    As a result, you could get the list you want:

    190710-image.png


    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.


0 additional answers

Sort by: Most helpful