How do I fix the syntax error in my varnumber Date formula (calculated date based on text-multiple choice field?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I want a field [Closing Date - 1] to autopopulate with a date that is based on a different date field plus a multiple choice field.

Basically I want [Closing Date – 1] to equal [Date Last Updated] plus [Closing Expection]. [Closing Expectation] is a multiple choice field, so I also need to convert the text into days/duration in the formula.

However, I am getting a syntax error!

varnumber([Closing Date - 1]=case([Closing Expectation - 1],
"1 Month",30,
"2 Months",60,
"6 Months",180,
"1 Year",365,
"Greater than 1 Year",548);
[Date Last Updated] + Days([Closing Expectation - 1])

 I have stared at this too long and still cannot find the syntax error. Please help! Thank you.
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I think this is what you are looking for:

var number NumDays = Case([Closing Expectation - 1],
  "1 Month", 30,
  "2 Months", 60,
  "6 Months", 180,
  "1 Year", 365,
  "Greater than 1 Year", 548
);

[Date Last Updated] + Days($NumDays)
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb
Thank you for your quick reply! The Syntax Error is gone, but nothing happened in my report. Should the formula include somewhere that is is supposed to change [Closing Date - 1] ?
I am very new to formulas.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
>but nothing happened in my report

I don't know what this means - certainly something shows up in this column of the report.
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb
It did add the correct date in my report, but is there a way to autopopulate the field so that it also shows up in the form?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I don't understand what you are saying. The formula I gave you should (1) display the correct value in a report and (2) on an edit form should automatically update as the [Closing Expectation - 1] input is modified while the edit form is open. Which of these two behaviors are you not seeing?
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb
Sorry, I didn't realize that the field was hidden from my form. All is well!! Thank you for your help!!!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You are working too hard - slack off for the rest of the week:

SubGenius Commercial
https://www.youtube.com/watch?v=Qt9MP70ODNw