Forum Discussion

KingslySamuel1's avatar
KingslySamuel1
Qrew Assistant Captain
8 years ago

Formula Help (Planned Duration & Estimated Date)

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.
App builders
Ask about Quickbase functionality
Formulas and functions
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    8 years ago
    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?
  • KingslySamuel1's avatar
    KingslySamuel1
    Qrew Assistant Captain
    8 years ago
    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.
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    8 years ago
    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.
  • KingslySamuel1's avatar
    KingslySamuel1
    Qrew Assistant Captain
    8 years ago
    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.
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    8 years ago
    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))))