Hello All Wonderful people,
I'm very new to this tool and in the phase of self-learning.
I've an excel sheet where I need to insert a new column (Expiration Range) and in that column I need to set a range of number of days.
In Excel I used this formula "=IF(AND(B2>0,B2<30),"0-30",IF(B2<=60,"31-60",IF(B2<=90,"61-90",IF(B2<=120,"91-120",IF(B2<=190,"121-190",IF(B2>190,">190","Null"))))))"
One more thing I wanted to infuse, where if the new Column would change the number 0 to "Completed or "Expired", if the "Overall Status is Completed/Expired.
Column B in my sheet is the number of days, here's a sample shot for ease-
Solved! Go to Solution.
Hi,
To add your "Expiration Range" column, add in a formula tool and add the following:
IF [Days To Expiration]>=0 && [Days To Expiration] <=30 Then "0-30"
ElseIF [Days To Expiration]>=31 && [Days To Expiration] <=60 Then "31-60"
ElseIF [Days To Expiration]>=61 && [Days To Expiration] <=90 Then "61-90"
ElseIF [Days To Expiration]>=91 && [Days To Expiration] <=120 Then "91-120"
ElseIF [Days To Expiration]>=121 && [Days To Expiration] <=190 Then "121-190"
ElseIF [Days To Expiration]>190 Then ">190"
Else Null()
Endif
I'm not sure I fully understood your second requirement but I think you were asking to overwrite the expiration range with the overall status?
To do that you can create another forumla which looks like this:
If [Overall Status] = "Completed" || [Overall Status] = "Epxired" Then [Overall Status]
Else [Expriration [Expiration Range]
Endif
Sweet! It worked and thanks a ton :)