Discussions

 View Only
Expand all | Collapse all

due date formula based on priority ...help

  • 1.  due date formula based on priority ...help

    Posted 10-24-2017 17:58

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



  • 2.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:01

    [the new date] =

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



  • 3.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:02

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

    Let us know how it goes.



  • 4.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:04

    That should probably go like this instead:

    [the new date] =

    Today()+Days([Priority])

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



  • 5.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:09

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




  • 6.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:05
    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.


  • 7.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:30

    [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



  • 8.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:34

    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.



  • 9.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:38

    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)



  • 10.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:40

    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.




  • 11.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:47

    No, sorry for the confusion.

    [Due Date] field is a formula date field
    [Priority] field is a numeric field

    I decide what the priority is based on the customers needs. I need the Due Date to automatically populate based on the Priority and the created date.

    Priority 1 projects need to be completed by NEXT DAY (created date + 1 day)

    Priority 10 projects need to be completed 10 days past the created date at the latest



  • 12.  RE: due date formula based on priority ...help

    Posted 10-24-2017 18:47

    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.



  • 13.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:00

    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



  • 14.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:01
    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


  • 15.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:07
    No confusion intended. I get it.


  • 16.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:09

    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.



  • 17.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:10

    Take it away, Matthew of cirrusops.



  • 18.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:22
    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.


  • 19.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:26

    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



  • 20.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:34
    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),
    )


  • 21.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:37


  • 22.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:42

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



  • 23.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:44
    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)


  • 24.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:48

    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?



  • 25.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:53
    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?


  • 26.  RE: due date formula based on priority ...help

    Posted 10-24-2017 19:56

    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.