Request for a formula to return a value based on several conditions

Newbie

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

Kelly Moore

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

https://help.smartsheet.com/function/and

https://help.smartsheet.com/function/or

Kelly Moore

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

https://help.smartsheet.com/function/and

https://help.smartsheet.com/function/or

Newbie

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