SP List - Dates using IF AND formula

Hi Bryan (Bryan123 ), trying to work out the correct formula gave me a headache so if you don't manage to find it you might want to consider an alternative method to calculate the date. And that method is to use a flow in Power Automate with conditions which will update the date calculation column with the relevant date when you press a button on the list item in SharePoint. It's not scary or difficult and it does do what you want it to do. 

In our list I have a Yes/No column for PTO and Date columns for the Attendance and CalcDate columns.

For the button, add a single line of text column and from the column header select Column setting then Format this column and paste in the following. You will later need to change the flow ID to your flow but the code to use is:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Calc Date",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"0529cdcd-5562-4643-a4d3-832b77a5fa25\"}"
  },
  "style": {
    "background-color": "#468259",
    "color": "white"
  }
}

The section above after id\": \"  is the ID you need to change.

OK so for the flow go into Power Automate and in the My Flows screen click New and Instant from blank then click Skip so you start with a totally blank flow.

The trigger is a SharePoint For a selected item and you select your site and the list. The first action is to Get that item and the info in it. So add a SharePoint Get item action. Again, select the site and list and select ID from the dynamic content box that pops up when you click in the ID field.

The next action is to add a Condition that will check if the item in the PTO column equals Yes. Select the PTO column from the dynamic content for the left box. For the middle box select is equal to. For the right hand criteria box you need to enter true.

If the PTO column is NOT equal to Yes (true) then do nothing, so just leave the red If no channel empty.

However in the If Yes green channel add another condition. Select the Attendance column for the left field. Select is less than for the middle field and enter 2020-10-1 in the right field.

In the green If yes channel add a SharePoint Update item action and in the  CalDate column type in 2020-12-31.

In the red If no column add another condition with 2 rows. the first is if Attendance is greater than or equal to 2020-10-1. The second row is Attendance is less than or equal to 2020-12-31.

Leave the red If no channel empty.

In the green If yes channel add a Compose control and from the expression tab of the dynamic content copy & paste in the following expression to calculate 90 days from the Attendance date column:

For the CalcDate column , from the dynamic content select the output of the Compose action. That will add a date 90 days ahead of the Attendance column.

So if you enter 2020-03-20 in the Attendance column and click the button, that will enter 2020-12-31 in to the CalcDate column. If you add 2020-12-03 into the Attendance column and click the button that will add 2021-03-03 into the CalcDate column.

I quite understand that it's not the solution you were asking for and is a route you might not want to go down, but maybe it will open up other opportunities for you that you might not have thought of. I hope it helps as a possible alternative. Come back with any questions about it.

Rob
Los Gallardos
Microsoft Power Automate Community Super User