question

SumiteshKumar avatar image
0 Votes"
SumiteshKumar asked SumiteshKumar commented

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

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-devoffice-sharepoint-server-developmentoffice-sharepoint-server-customization
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

1 Answer

YiLu-MSFT avatar image
0 Votes"
YiLu-MSFT answered SumiteshKumar commented

Hi @SumiteshKumar
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.



image.png (36.9 KiB)
image.png (10.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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