Skip to main content
Question

due date formula based on priority ...help

  • October 24, 2017
  • 0 replies
  • 85 views

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

    This topic has been closed for replies.

    • ChrisChris
    • Registered
    • October 24, 2017

    [the new date] =

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

    • MCFNeil
    • Registered
    • October 24, 2017
    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.
    • MichelleCoslet
    • Author
    • Registered
    • October 24, 2017

    [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

    • ChrisChris
    • Registered
    • October 24, 2017

    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.

    • MCFNeil
    • Registered
    • October 24, 2017
    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.
    • MichelleCoslet
    • Author
    • Registered
    • October 24, 2017

    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

    Terms & ConditionsAccessibility statement