Forum Discussion

MichelleCoslet's avatar
MichelleCoslet
Qrew Assistant Captain
8 years ago

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

App builders
Ask about Quickbase functionality
Formulas and functions
  • ChrisChris's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago

    [the new date] =

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

    • ChrisChris's avatar
      ChrisChris
      Qrew Assistant Captain
      8 years ago

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

      Let us know how it goes.

    • ChrisChris's avatar
      ChrisChris
      Qrew Assistant Captain
      8 years ago

      That should probably go like this instead:

      [the new date] =

      Today()+Days([Priority])

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

    • MichelleCoslet's avatar
      MichelleCoslet
      Qrew Assistant Captain
      8 years ago

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


  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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's avatar
    MichelleCoslet
    Qrew Assistant Captain
    8 years ago

    [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's avatar
    ChrisChris
    Qrew Assistant Captain
    8 years ago

    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.

    • MichelleCoslet's avatar
      MichelleCoslet
      Qrew Assistant Captain
      8 years ago

      how does it know to add the certain amount of days then, don't I need to specify how many days I need added to the date created?


      if it is a priority 5 project that I started today, I need the due date to show  10/30/2017. that reminds me, I am basing this off  of a standard m-f work week (not to include sat & sun)

    • ChrisChris's avatar
      ChrisChris
      Qrew Assistant Captain
      8 years ago

      Is not [Priority] a formula field you created that has contains some 'x' number of days?

      If so, then that is the parameter you pass to the Days() function. That makes it more dynamic and no need to have redundant lines of code.


    • ChrisChris's avatar
      ChrisChris
      Qrew Assistant Captain
      8 years ago

      if(

      dayofweek([Date Created]+Days([Priority]))=6,

      [Date Created]+Days([Priority])+2,

      dayofweek([Date Created]+Days([Priority]))=0,

      [Date Created]+Days([Priority])+1,

      [Date Created]+Days([Priority])

      )


      So this code adjusts the Due Date to Monday if the Priority date lands on a Saturday or a Sunday. 0=sunday, 6=Saturday. The default value will be Date Created plus priority days.

  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    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's avatar
    MichelleCoslet
    Qrew Assistant Captain
    8 years ago

    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

    • MCFNeil's avatar
      MCFNeil
      Qrew Captain
      8 years ago
      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),
      )
    • MichelleCoslet's avatar
      MichelleCoslet
      Qrew Assistant Captain
      8 years ago

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