Formula Help (Planned Duration & Estimated Date)

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
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.
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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?
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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.
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
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)))) 
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
yes, works perfectly fine. Thank you.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Excellent, I'm glad to hear that helped Kingsly
(Edited)