Discussions

Expand all | Collapse all

Formula Help (Planned Duration & Estimated Date)

  • 1.  Formula Help (Planned Duration & Estimated Date)

    Posted 03-07-2018 12:52
    Hi,

    I have 2 fields that need to be considered for calculation to pull up a "duration" (in # of days) and "estimated date" (in date) in the 3rd and 4th field, respectively.

    Field 1 (text-multiplechoice) "Template Type":
    1. A
    2. B
    3. C
    4. D
    5. E
    Field 2 (date) "Start Date"

    Field 3 (duration) "Planned Duration"
    • If the "template type" is selected as "A" or "B", I want the "planned duration" to add "30 days" (including weekends) to the "start date" and give me an output in "# of days"
    • If the "template type" is selected as "C", I want the "planned duration" to add "60 days" (including weekends) to the "start date" and give me an output in "# of days"
    • If the "template type" is selected as "D", I want the "planned duration" to add "90 days" (including weekends) to the "start date" and give me an output in "# of days"
    • If the "template type" is selected as "E", I want the "planned duration" to add "120 days" (including weekends) to the "start date" and give me an output in "# of days"
    Field 4 (date) "Estimated Date"
    • If the "template type" is selected as "A" or "B", I want the "estimated date" to add "30 days" (including weekends) to the "start date" and give me an output in "date"
    • If the "template type" is selected as "C", I want the "estimated date" to add "60 days" (including weekends) to the "start date" and give me an output in "date"
    • If the "template type" is selected as "D", I want the "estimated date" to add "90 days" (including weekends) to the "start date" and give me an output in "date"
    • If the "template type" is selected as "E", I want the "estimated date" to add "120 days" (including weekends) to the "start date" and give me an output in "date"
    Can you help me with the formula that needs to go in Field 3 and Field 4? Thank you.


  • 2.  RE: Formula Help (Planned Duration & Estimated Date)

    Top
    Contributor
    Posted 03-07-2018 14:44
    Hi Kingsly,

    I just wanted to ask for some clarification on the formula for field 3.
    • If the "template type" is selected as "A" or "B", I want the "planned duration" to add "30 days" (including weekends) to the "start date" and give me an output in "# of days"
    So if the template is A or B you want to take the Start Date and add the "planned duration to it" to get you a # of days. a # of days from Today? a # of days from another date field? Should it be counting down each day to that new date 30 days after the start date? Or should the planned duration field just read 30 days when A or B is chosen and just read 60 days when the option c is chosen and not change?


  • 3.  RE: Formula Help (Planned Duration & Estimated Date)

    Posted 03-07-2018 16:28
    Thank you Evan. As I started typing answer to your question, I felt foolish. What a dumb question I have asked on Field 3. You are right, it is fixed # of days and I don't need a complex formula on Field 3. I will just make a form controlled number to show up on the field based on the selected "Template Type". Just advise me on Field 4. Thank you for pointing out the obvious.


  • 4.  RE: Formula Help (Planned Duration & Estimated Date)

    Top
    Contributor
    Posted 03-07-2018 16:43
    Hi Kingsly,

    Not a problem at all, I just wanted to make sure there wasn't a component missing that would change the response. For Part 4 you should be able to use a formula like below in a Formula-Date:

    If([Template Type]="A" or [Template Type]="B", [Start Date]+Days(30),
    If([Template Type]="C", [Start Date]+Days(60),
    If([Template Type]="D",[Start Date]+Days(90),
    If([Template Type]="E"[Start Date]+Days(120), null))))
    This assumes that Template Type is a Text field and Start Date is a Date field. If Start Date is instead a Work Date field you may need to convert it to a Date field first using the ToDate conversion. I hope this suggestion is helpful Kingsly.


  • 5.  RE: Formula Help (Planned Duration & Estimated Date)

    Posted 03-07-2018 16:49
    Thanks Evan. The Field 4 is changed to "Formula-Date" field. And, yes, the "Start Date" is a "date field" and "Template Type" is a "Text (multiple choice drop down)" field. But, it is throwing up a syntax error when I used the above formula.


  • 6.  RE: Formula Help (Planned Duration & Estimated Date)

    Top
    Contributor
    Posted 03-07-2018 16:51
    Looking at my formula again it is probably because I forgot a comma after ="E". Try this slight adjustment again and if it still throws a syntax error let me know. 

    If([Template Type]="A" or [Template Type]="B", [Start Date]+Days(30),
    If([Template Type]="C", [Start Date]+Days(60),
    If([Template Type]="D",[Start Date]+Days(90), If([Template Type]="E",[Start Date]+Days(120), null)))) 


  • 7.  RE: Formula Help (Planned Duration & Estimated Date)

    Posted 03-07-2018 16:56
    yes, works perfectly fine. Thank you.


  • 8.  RE: Formula Help (Planned Duration & Estimated Date)

    Top
    Contributor
    Posted 03-07-2018 18:21
    Excellent, I'm glad to hear that helped Kingsly