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
-
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))))
Answers
-
@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 isX
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. -
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))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!