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

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")