Request for a formula to return a value based on several conditions
I am new to Smartsheet and wanted assistance in developing a formula based on the below conditions. My attempts thus far is unfortunately returning a error message.
If the age of the document is <=730 and the type of the document is Procedure, then return the value of 6
If the age of the document is between 731 and 1440 and the type of the document is Procedure, then return the value of 3
If the age of the document is >1441 and the type of the document is Procedure, then return the value of 6
If the age of the document is <=730 and the type of the document is Work Instruction, then return the value of 3
If the age of the document is between 731 and 1440 and the type of the document is Work Instruction, then return the value of 0
If the age of the document is >1441 and the type of the document is Work Instruction, then return the value of 6
If the age of the document is <=730 and the type of the document is Form, then return the value of 1
If the age of the document is between 731 and 1440 and the type of the document is Form, then return the value of 0
If the age of the document is >1441 and the type of the document is Form, then return the value of 1
Best Answer

Hey @Newbie
Happy to help.
Try this formula. I noticed you have a gap between at 1441  I took a guess at which way you wanted it to work. Test the formula thoroughly and we can tweak wherever is needed. It looks intimidating just because it's long. You'll find I grouped the ANDs and the ORs by what result it produced.
=IF(AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row <= 730), 6, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row <= 730), AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 3, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441), AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 0, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row <= 730), 1, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 1440), 1, IF(AND(OR([Document Type]@row = "Procedure", [Document Type]@row = "Work Instruction"), [Age of Document (Days)]@row > 1440), 6))))))
If you're interested, you can find information on IF/AND or IF/OR below
cheers,
Kelly
Answers

Hey @Newbie
Happy to help.
Try this formula. I noticed you have a gap between at 1441  I took a guess at which way you wanted it to work. Test the formula thoroughly and we can tweak wherever is needed. It looks intimidating just because it's long. You'll find I grouped the ANDs and the ORs by what result it produced.
=IF(AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row <= 730), 6, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row <= 730), AND([Document Type]@row = "Procedure", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 3, IF(OR(AND([Document Type]@row = "Work Instruction", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441), AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 730, [Age of Document (Days)]@row < 1441)), 0, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row <= 730), 1, IF(AND([Document Type]@row = "Form", [Age of Document (Days)]@row > 1440), 1, IF(AND(OR([Document Type]@row = "Procedure", [Document Type]@row = "Work Instruction"), [Age of Document (Days)]@row > 1440), 6))))))
If you're interested, you can find information on IF/AND or IF/OR below
cheers,
Kelly

Thank you Kelly for taking the time to answer my question. The above formula worked exactly how I wanted it to.
I also now know were I went wrong in my earlier attempts. I did not use the AND and OR function wisely. Lesson learnt.
Thank you
Help Article Resources
Categories
Check out the Formula Handbook template!