IF Statements in SharePoint List

Question

I have created a flow within Power Automate that when a Form is submitted it creates an entry into a SharePoint List. Once the entry has been approved a new entry is created in a new SharePoint List, which in turn creates another item in another list (confused, I am). I am trying to create an IF statement based on the following criteria using the following Column Headings - 'Last review date', - 'Residual Score' & 'Next review date'.

What I would like to happen is:

If the Residual Score column value is 16 or over - then the Next Review Date Column needs to be automatically populated with the date 3 months later from the last review date If the Residual Score column value is between 8-15 - then the Next Review Date Column needs to be automatically populated with the date 6 months later from the last review date Anything score 7 or below then the Next Review Date Column needs to be automatically populated with the date 12 months later from the last review date.

Is this in the art of the possible or am I trying to run before I can walk.

Any advice would be greatly appreciated. Thanks in advance.

Answer 1

You should try below formula:

=IF(AND(ISBLANK([Residual Risk Score]),[Date Last Reviewed]<1),"",IF([Residual Risk Score]>15,DATE(YEAR([Date Last Reviewed]),MONTH([Date Last Reviewed])+3,DAY([Date Last Reviewed])),IF(AND([Residual Risk Score]>7,[Residual Risk Score]<16),DATE(YEAR([Date Last Reviewed]),MONTH([Date Last Reviewed])+6,DAY([Date Last Reviewed])),IF([Residual Risk Score]<8,DATE(YEAR([Date Last Reviewed]),MONTH([Date Last Reviewed])+12,DAY([Date Last Reviewed])),""))))

Make sure that you use correct field's display name in formula, in my case Residual Risk Score and Date Last Reviewed is the display name of the column.

Official Documentation: Calculated Column

Important:

All example formulas in this topic use commas "," as the parameter delimiter character. In some countries, the comma is reserved for use as the decimal mark. In such countries, users creating a calculated field must use semi-colons ";" as the delimiter character. Regardless of which character is used when the field is created, the formula works on lists in SharePoint websites anywhere in the world. SharePoint automatically changes the delimiter character to the one that is appropriate for the language/culture of the current page. For example, suppose the following formula is created on a website whose culture setting is fr-fr (France): =IF(Number1>Number2;5;10). If the website's culture is then changed to en-us (United States), the formula changes automatically to: =IF(Number1>Number2,5,10).

Following should be configuration for the column:

Following is the output of this formula: