due date formula based on priority ...help

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered

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

Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

[the new date] =

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

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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

Let us know how it goes.

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

That should probably go like this instead:

[the new date] =

Today()+Days([Priority])

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

Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

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


Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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.
Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

[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

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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)
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

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

Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Use for formula "WeekDayAdd"

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

Thats it.
WeekdayAdd (Date d, Number n)

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. 

WeekdayAdd(ToDate("6/20/2003"), 2) returns 6/24/2003

WeekdayAdd(ToDate("6/24/2003"), -2) returns 6/20/2003

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)
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
No confusion intended. I get it.
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

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.

Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Take it away, Matthew of cirrusops.

Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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;
WeekdayAdd (Date d, Number n)

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.
Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

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

Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

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

Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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)
Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

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)
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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?
Photo of mkosek

mkosek

  • 796 Points 500 badge 2x thumb

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.