SharePoint list calculated column 3 validations for IF statement

Patel, Somesh [CCC-OT] 1 Reputation point
2022-10-04T21:29:28.52+00:00

I have a SP list that contains software installation data. There is a calculated column that calculates price. There is a column called Installation Date which has 2 choices either Before Sep 30, 2020 or After Sep 30, 2020 in String. There is a request type field which has 2 choices New or Renew. The Charge Date field contains Date.

I created this formula which works but is missing the logic to filter Request Type field. I wanted some assistance on updating the IF statement.

=IF([Installation Date]="Before Sep 30, 2020",[Lic Qty]*750,(([Lic Qty]*430)/TEXT([Charge Date],"mm")))

For Software installed before Sep 30, 2020 and Request Type is New: cost will be $750
For Software installed before Sep 30, 2020 and Request Type is Renew: cost will be $430
For Software installed After Sep 30, 2020 : Price will be $430 per license per year. Pro-rated for the current year.
Thank you for your time.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,651 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,673 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Xuyan Ding - MSFT 7,561 Reputation points
    2022-10-05T07:28:31.993+00:00

    Hi @Patel, Somesh [CCC-OT] ,

    Since I'm not very clear about the specific proportional relationship you said, I didn't create the Charge Date column during the test. But in the formula below, I use $3061 to represent the software price after 9.30. I believe your focus must be on the previous loop, you just need to replace $3061 in the formula with your ratio.

    Here is the formula I used and the test result: 

    IF(AND([Installation Date]="Before Sep 30,2020", [request type]="New"), [Lic Qty]*750, IF([Installation Date]="After Sep 30,2020",[Lic Qty]*3061,[Lic Qty]*430))

    247657-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.