Forum Discussion

Copper Contributor

Jun 28, 2023

Solved

Nested IF's based on various criteria

Hey folks -brain is not working this morning. I have various columns in which I am trying to create a calculation of time buckets for a specific column only if another criteria is not met. so...

excel

NikolinoDE
Jun 28, 2023
Mike Button
=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed", [@[Status]]<>"Done"), IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", "")))),"")

Make sure to replace the range references ([@Status] and [@Days Open]) with the actual column references in your worksheet.
This formula checks if the Status column is not equal to "Cancelled", "Canceled", "Closed", or "Done". If the condition is true, it applies the time bucket calculation based on the Days Open column. If the condition is false, it returns an empty string. The text was created with the help of AI.

NikolinoDE

Platinum Contributor

Jun 28, 2023

Mike Button

=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed", [@[Status]]<>"Done"), IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", "")))),"")

Make sure to replace the range references ([@Status] and [@Days Open]) with the actual column references in your worksheet.

This formula checks if the Status column is not equal to "Cancelled", "Canceled", "Closed", or "Done". If the condition is true, it applies the time bucket calculation based on the Days Open column. If the condition is false, it returns an empty string. The text was created with the help of AI.

Mike Button
Copper Contributor
Jun 28, 2023
NikolinoDElooks like the original calculation failed as well
Mike Button
Copper Contributor
Jun 28, 2023
Something like this is what I want todo
=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed",(TODAY()-[@Created]),"Closed"))
- NikolinoDE
  Platinum Contributor
  Jun 28, 2023
  =IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed"),
  TODAY()-[@Created],
  "Closed")
Mike Button
Copper Contributor
Jun 28, 2023
So this might be stupid, what/how would I modify this to to check the status, and if neither of those I do a calculation of determine how long it is open. So if not Cancelled to do a simple Created-Today() calcuation.