Complicated IF and Text to Number formula

  • 0
  • 2
  • Question
  • Updated 8 months ago
  • Answered
I have a complicated formula that I need help with. I can write the formulas in excel, but still haven't figured out how to write them in QB. 

I have users entering data into the following fields:
Run Time=Text drop down 60,90,150
Start Date
Notification#1
Resubmittal #2
Response -checkbox
Relates-checkbox
Notification#2
Resubmittal #3


I need formulas for other fields of: Days Remaining, Expiration Date  and Notification1 forecast.

Here's the criteria. i have bolded and italicized field names.

Result 1: Days Remaining- total number of days used between today() and Start Date minus Run Time. So Start Date+ Run Time - Today.  Time between Notification(s) and Resubmittal(s) does not count towards time used however time between Start Date and Notification 1 does. Time between Resubmittal 2 and Notification 1 does not. 
Example:  for Days Remaining: Start Date = 1/15/18, Run Time=90 days, Today = 4/3/18, result = 12 days, but if Notification 1 was 2/10/18 and Resubmittal 2 was 2/28/18, 18 days need to be added to Days Remaining=30 days. 
There may be a second round of Notification/Resubmittal which further complicates everything. 
Days Remaining is then conditional on 2 checkboxes, Response and Relates.  Both boxes will be checked at the same time. 
If Response=Yes and Relates = Yes- Time between Notification 2 and Submittal 3 gets added to Days Remaining.
If Response = No and Relates = Yes - Time between Notification 2 and Submittal 3 gets added to Days Remaining.
If Response = Yes and Relates = No- No, then no time is added
If Response = No and Relates = No, then no time is added.
Result 2: Expiration date. Number of days used, plus start date.
Using same example data from above, Start Date= 1/15/18, Days used = (Submittal 2-Notification1= 18 + Run Time (90) = 5/2/18
Resubmittal and Notification may remain blank.
Result 3: Notification 1 forecast = Start date+ Run Time unless Start Date =8/8/1988, then Notification 1 Forecast = blank


NOTE: There's a checkbox called Not Required. Which has Dynamic form rule set up for checking Not Required box if Start Date = 8/8/1988


Can anyone help write these formulas, I have issues with the type conversion as many of the fields are based off the Run Time, which is a TEXT drop down. 

I appreciate any and all help I can get. 
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
  • overwhelmed and confused.

Posted 8 months ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Hey Pam,

Hope you are doing well.  I'm going to give this a crack.

It would help if you set up 'variables' and then added them together later.  It helps with reading and debugging.

Variables are formatted as:
var {type} Name=evaluation;

I also like to start with the most simple and build the complexity.
We need to find the "Due Date" even though that keeps moving and we only worry about the # of days that it gets pushed.

Field [Days Remaining] formula:
""""""
var number RunTime=ToNumber([Run Time]);
var number NoteOneToResubTwo=ToDays([Resubmittal #2]-[Notification #1]);
var number NoteTwoToResubThree=If([Relates]=true, ToDays([Resubmittal #3]-[Notification #2]), null);

var date DueDate=
[Start Date]+Days($RunTime)+Days($NoteOneToResubTwo)+Days($NoteTwoToResubThree);

ToDays($DueDate-Today())

""""""

Field [Expiration Date] formula: coming soon
Field [Notification 1 Forecast] formula: coming soon
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
The Nz's work like a charm! Now that all of that is working like magic, I see I missed the final step to the calculation. Once Approved is entered, then it needs to stop calculating and just leave the data at whatever the DueDate and Remaining days were.  Do I need to add a new field for "Stopped" or can it just be rolled into an If statement?
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Here's an idea...

Try using an If for the last portion of the equation.

If(IsNull([Approved]),
ToDays($DueDate-Today()),
ToDays($DueDate-[Approved]))

Basically it will substitute the [Approved] date rather than Today()
Photo of Pamela Bray

Pamela Bray

  • 272 Points 250 badge 2x thumb
I think it's missing something, there needs to be an if run time=blank, leave Expiration and Days Remaining blank.

I tried to add an If at the beginning of the expiration formula, but get 
"Expecting a bool, but found number" error

If($RunTime)="","",[Start Date] +Days($RunTime)+ Days($NoteOneToResubTwo) +Days($NoteTwoToResubThree)

Here's what I have for Run Time:

var date DueDate=
[Start Date]+(Days($RunTime)+ Days($NoteOneToResubTwo)+ Days($NoteTwoToResubThree));
If(IsNull([Approved]),
ToDays($DueDate-Today()),
ToDays($DueDate-[Approved])
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Try this correction....

If(IsNull($RunTime),  null, [Start Date] +Days($RunTime)+ Days($NoteOneToResubTwo) +Days($NoteTwoToResubThree))

Keep in mind that date and numeric fields use the "null" value besides the "".
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Or it might be a "0"

If($RunTime=0, null, [Start Date] +Days($RunTime)+ Days($NoteOneToResubTwo) +Days($NoteTwoToResubThree))