due date formula based on priority ...help
[Priority]=1,[Due Date] Today() + Days(1) [Priority]=2,[Due Date] Today() + Days(2) [Priority]=3,[Due Date] Today() + Days(3) [Priority]=4,[Due Date] Today() + Days(4) [Priority]=5,[Due Date] Tod...
Forum Discussion
that is my point, the priority value IS NOT the number of days I am adding
The number of days I am adding is based off the priority. Pretend I want to add 7 days to the [date created] if priority is 1. Would that change the formula you are proposing? I would think it would. it just so happens that priority 1 through 4 are the same value as the days I am needing to add. That is not the case for Priority 5-9
Priority 1 = +1 day
Priority 2 = +2 day
Priority 3 = +3 day
Priority 4 = +4 day
Priority 5 = +4 day
Priority 6 = +5 day
Priority 7 = +6 day
Priority 8 = +7 day
Priority 9 = +8 day
Priority 10 = +10 day
- Oh wow, I didn't even notice they don't match for 5-9. Thats interesting.
Nonetheless we are on the right track.
We will want to use a Case formula combined with the other logic.
Case([Priority],
1, WeekDayAdd(ToDate([Date Created]), 1),
2, WeekDayAdd(ToDate([Date Created]), 2),
3, WeekDayAdd(ToDate([Date Created]), 3),
4, WeekDayAdd(ToDate([Date Created]), 4),
5, WeekDayAdd(ToDate([Date Created]), 4),
6, WeekDayAdd(ToDate([Date Created]), 5),
7, WeekDayAdd(ToDate([Date Created]), 6),
8, WeekDayAdd(ToDate([Date Created]), 7),
9, WeekDayAdd(ToDate([Date Created]), 8),
10, WeekDayAdd(ToDate([Date Created]), 10),
) correct - to simplify the formula I thought about setting the priority to whatever value days I need added but that wont work for us.
I tried your formula above and I get the following syntax error
"Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets."
- Remove the last comma, or add null
Case([Priority],
1, WeekDayAdd(ToDate([Date Created]), 1),
2, WeekDayAdd(ToDate([Date Created]), 2),
3, WeekDayAdd(ToDate([Date Created]), 3),
4, WeekDayAdd(ToDate([Date Created]), 4),
5, WeekDayAdd(ToDate([Date Created]), 4),
6, WeekDayAdd(ToDate([Date Created]), 5),
7, WeekDayAdd(ToDate([Date Created]), 6),
8, WeekDayAdd(ToDate([Date Created]), 7),
9, WeekDayAdd(ToDate([Date Created]), 8),
10, WeekDayAdd(ToDate([Date Created]), 10)
)
OR
Case([Priority],
1, WeekDayAdd(ToDate([Date Created]), 1),
2, WeekDayAdd(ToDate([Date Created]), 2),
3, WeekDayAdd(ToDate([Date Created]), 3),
4, WeekDayAdd(ToDate([Date Created]), 4),
5, WeekDayAdd(ToDate([Date Created]), 4),
6, WeekDayAdd(ToDate([Date Created]), 5),
7, WeekDayAdd(ToDate([Date Created]), 6),
8, WeekDayAdd(ToDate([Date Created]), 7),
9, WeekDayAdd(ToDate([Date Created]), 8),
10, WeekDayAdd(ToDate([Date Created]), 10),
null) I chose to just remove the last comma (also tried the null option)
new error: The types of the arguments or the number of arguments supplied do not meet the requirements of the function Case.
Do I need to change the due date to "date/time - formula" to match the [created date] format?
- Is the [Priority] field an actual 'Numeric' field or just numbers in a 'text' field?
If its a text field, you will need to add quotes around the number being evaluated
"1", WeekdayAdd......
"2", Weekda.....
The Due Date field is a "Formula-Date" field correct? It is a multiple choice field. Adding the quotes fixed the issue.
THANK YOU!!!
Sorry for the headache - I do not envy you. I would have given up on me long ago.
