# due date formula based on priority ...help

• 1
• Question
• Updated 2 years ago

[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] Today() + Days(4)
[Priority]=6,[Due Date] Today() + Days(5)
[Priority]=7,[Due Date] Today() + Days(6)
[Priority]=8,[Due Date] Today() + Days(7)
[Priority]=9,[Due Date] Today() + Days(8)
[Priority]=10,[Due Date] Today() + Days(10)

Here is what I am trying to accomplish:

If Priority is equal to 1, set the due date to today + 1 day

If Priority is equal to 2, set the due date to today + 2 days

etc...

I am sure I am missing some commas, brackets, parenthesis and who knows what else. I wasn't able to find another post that was as similar as mine to copy.

I keep getting a syntax error.

• 918 Points

Posted 2 years ago

• 1
• 4,430 Points

[the new date] =

[Due Date] Today() + Days([Priority])

• 4,430 Points

make [the new date] a formula date field and put the code below into it.

Let us know how it goes.

• 4,430 Points

That should probably go like this instead:

[the new date] =

Today()+Days([Priority])

I'm not certain what [Due Date] is supposed to be.

• 918 Points

Due date is a formula date field I created to automatically populate based on whatever priority the project is set at.

So in this case it is for quoting purposes. If our customer wants the quote for the job returned as soon as possible, I would set priority to 1.

That will automatically populate the DUE DATE field today + I day. This way everyone in our team knows the project must be completed by tomorrow (Next, I will setup notifications/subscriptions if the project is past due).

• 31,698 Points
Assuming your [Due Date] field is a formula date field.
and your [Priority] field is a numeric field....
You can simplify this greatly.

Today()+Days([Priority])

However, using the "Today()" option will constantly update/change the due date, because Today's date always changes.

I'd recommend using a static date, like Date Created, or some other date that the user enters.
• 918 Points

[Date Created] + Days(1)  ([Priority]),1
[Date Created] + Days(2)([Priority]),2
[Date Created] + Days(3)([Priority]),3
[Date Created] + Days(4)([Priority]),4
[Date Created] + Days(4)([Priority]),5
[Date Created] + Days(5)([Priority]),6
[Date Created] + Days(6)([Priority]),7
[Date Created] + Days(7)([Priority]),8
[Date Created] + Days(8)([Priority]),9
[Date Created] + Days(10)([Priority]),10

Still getting the syntax error

• 4,430 Points

Take out the parenthesis that have numbers inside them. Make it like this:

"[Date Created]+Days([Priority])". Sans quotes.

this ^^^ is the only line of code you need.

(Edited)
• 908 Points

no - the number I set priority to is not meant to be the number of days added. The number of days added needs to come from the formula.

If I set the project to PRIORITY 1 (meaning highest priority), I need the DUE DATE to display 10/25/2017 (assuming I created the project today)

So the formula should *add 1 day* to the [date created] IF the priority is set to 1

*add 2 days* to the [date created] IF the priority is set to 2

*add 3 days* to the [date created] IF the priority is set to 3

• 31,698 Points

`WeekDayAdd(ToDate([Date Created]), [Priority])`

Thats it.

Description: Returns the date that is n weekdays past the given date d.  n may be negative to move backward in time.

Example: WeekdayAdd([Start], [Duration]) returns the date that results if you add the value in the Duration field to the date in the Start field and count only weekdays.

If you have a date field named "Start Date" and that field has a value of 6/23/2003, then WeekdayAdd([Start Date], -2) returns 6/20/2003
(Edited)
• 4,430 Points
No confusion intended. I get it.
• 908 Points

how isn't it an IF statement though?  The priority the project is set at affects how many days to add to the [date created]

I would have assumed the formula would look more like this (Except written correctly since I am still getting syntax errors)

if(
[Priority]1, [Date Created]+Days(1),
[Priority]2, [Date Created]+Days(2),
[Priority]3, [Date Created]+Days(3),
[Priority]4, [Date Created]+Days(4),
[Priority]5, [Date Created]+Days(4),
[Priority]6, [Date Created]+Days(5),
[Priority]7, [Date Created]+Days(6),
[Priority]8, [Date Created]+Days(7),
[Priority]9, [Date Created]+Days(8),
[Priority]10, [Date Created]+Days(10))

this should "change" the [due date] field in which I am writing this formula for, to the created date + x amount of days. x amount of days depends on the priority

priority 1 = +1 day

priority 2 = +2 days

etc

Maybe i'll just tackle this another day.

• 4,430 Points

Take it away, Matthew of cirrusops.

• 31,698 Points
Mkosek,

What Chris and I were trying to explain is that you don't need to have a long equation to evaluate the priority value if that value is the number of days you are adding.

i.e. If Priority = 1, add 1.

So you don't need a long equation of "If" statements, rather you can insert the [Priority] directly into the equation.

+Days([Priority])

Now to take it a step further you want to have only weekdays listed as the result.  So we use the formula;

Keep in mind that your "Date" needs to be just a date and not date/time.
So I use the conversion of "ToDate"  to take the time our of the date/time field of [Date Created] to only return the date;
ToDate([Date Created])

Combining all of the above you will have a one line formula that will dynamically update based on date created and priority, and returning a weekday value.

`WeekdayAdd( ToDate([Date Created]), [Priority])`

If you put that line, and only that line in your formula date field of [Due Date]  I feel confident you will get the result you are looking for.

I apologize for the confusion that we might have brought to the original question.
• 918 Points

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

• 918 Points

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."

• 31,698 Points
Remove the last comma, or add null

Case([Priority],
)

OR

Case([Priority],
null)
• 918 Points

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?

(Edited)
• 31,698 Points
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