1

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"))) 
  1. For Software installed before Sep 30, 2020 and Request Type is New: cost will be $750
  2. For Software installed before Sep 30, 2020 and Request Type is Renew: cost will be $430
  3. 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.

2 Answers 2

0

Use below formula for your calculated column:

=IF([Installation Date]="Before Sep 30, 2020",IF([Request Type]="New",[Lic Qty]*750,[Lic Qty]*430),([Lic Qty]*430)/MONTH([Charge Date]))

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Microsoft official documentations:

  1. Calculated Field Formulas.
  2. Examples of common formulas in lists.
1
  • Thank you very much, it works as needed.
    – Somesh P
    Oct 6, 2022 at 16:27
0

I think this might be what you're after. To make it a bit simpler, I've reversed your logic. Instead of starting with the "Before" scenario, I've started with the "After", which means I don't need to use multiple conditions.

=IF([Installation Date] = "After Sep 30, 2020",([Lic Qty] * 430) / Month([Charge Date]),IF([Request Type] = "New", [Lic Qty] * 750, [Lic Qty] * 430))
1
  • Thank you very much, I see, this is a great way to do it :)
    – Somesh P
    Oct 6, 2022 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.