Can someone help correct this formula

I have been trying to calculate a due date based on the date an issue was received and the priority.I have entered the following:

=WORKDAY(IF([Priority]10, "red", [Date Received]10, 0), IF([Priority]10, "Yellow", [Date Received]10, 2), IF([Priority]10, "Green", [Date Received]10, 4), IF([Priority]10, "blue", [Date Received]10, 14))

and I get "incorrect data set" as my message. Any ideas?

Best Answer

Answers

  • Mary_A
    Mary_A ✭✭✭✭✭✭
    edited 04/27/20

    @Carol Suhar This is a useful help page for debugging formula errors:

    To help you troubleshoot this error, I would recommend you get it working correctly with one priority (one IF statement) rather than going for the whole enchilada with multiple ifs. That will help you to debug your formula for one "case." Once you get it working, you can then expand to handle all of your use cases.

    Date calculations are tricky in Smartsheet, this has been a useful article for me:

    Also, it would be helpful if you could put into words what you are trying to accomplish. It appears you are trying to calculate a DUE DATE based on when a request Date Received and the Priority applied to that task. IF this describes is what you are trying to do, you may need to use the AND function in your cell formula.

    IF Priority is X AND Date Received is in the last X days, Due Date is (Date Received + X) days.

    This portion of the use case is tricky "Date Received is in the last X days" assuming that is what you are trying to do. Not quite sure if that is possible using something like the TODAY function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!