Can someone help correct this formula

Carol Suhar

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?

Paul Newcome

It looks like you want to change how many days are added based on the priority, but you are keeping the same start date.

=WORKDAY([Date Received]@row, number of days to add)

The number of days to add is where you want your nested IF statement.

=IF(Priority@row = "Red", 0, IF(Priority@row = "Yellow", 2, IF(Priority@row = "Green", 14)))

So now that we have our IF working to generate the numbers of days to add, we can drop it into our WORKDAY function:

=WORKDAY([Date Received]@row, IF(Priority@row = "Red", 0, IF(Priority@row = "Yellow", 2, IF(Priority@row = "Green", 14))))

Mary_A

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

https://help.smartsheet.com/articles/2476176-formula-error-messages#incorrectargumentset

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:

https://help.smartsheet.com/articles/2477601-use-formulas-perform-calculations-dates

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.

Paul Newcome

It looks like you want to change how many days are added based on the priority, but you are keeping the same start date.

=WORKDAY([Date Received]@row, number of days to add)

The number of days to add is where you want your nested IF statement.

=IF(Priority@row = "Red", 0, IF(Priority@row = "Yellow", 2, IF(Priority@row = "Green", 14)))

So now that we have our IF working to generate the numbers of days to add, we can drop it into our WORKDAY function:

=WORKDAY([Date Received]@row, IF(Priority@row = "Red", 0, IF(Priority@row = "Yellow", 2, IF(Priority@row = "Green", 14))))