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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!