# Discussions

View Only

## Complicated IF and Text to Number formula

• #### 1.  Complicated IF and Text to Number formula

Posted 04-03-2018 23:02
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
Resubmittal #2
Response -checkbox
Relates-checkbox
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.

• #### 2.  RE: Complicated IF and Text to Number formula

Posted 04-04-2018 18:27
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 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

• #### 3.  RE: Complicated IF and Text to Number formula

Posted 04-04-2018 18:30
Field [Expiration Date] formula: Basically what I called 'DueDate' above.

var number RunTime=ToNumber([Run Time]);
var number NoteTwoToResubThree=If([Relates]=true, ToDays([Resubmittal #3]-[Notification #2]), null);

[Start Date]+Days(\$RunTime)+Days(\$NoteOneToResubTwo)+Days(\$NoteTwoToResubThree);

• #### 4.  RE: Complicated IF and Text to Number formula

Posted 04-04-2018 18:33

var number RunTime=ToNumber([Run Time]);

If([Start Date]=Date(1988, 8, 8),
null,
[Start Date]+Days(\$RunTime))

• #### 5.  RE: Complicated IF and Text to Number formula

Posted 04-04-2018 18:34
I did my best without any formula editor, so I might have missed a comma or something.  Let me know if you have any issues.  Or Eric could take it from here to make it work.

• #### 6.  RE: Complicated IF and Text to Number formula

Posted 04-04-2018 21:14
Hi Matt,

Good to hear from you, thanks for tackling this!!!
There's no way I would have come up with the formulas you did.

I had to tweak the Notification 1 forecast slightly to [Start Date]+ days(30), as this one doesn't rely on the Run Time field. It was that first part of converting the 8/8/1988 date that had me stumped.

How do I tweak the other two to show expiration date and days remaining if Notification 1 is blank? There will be many times when a notification isn't required, or we haven't received it yet and right now the expiration and days remaining are blank (even if i put dates in all the other fields). I only get data if the Response box is checked AND there's dates in all other fields.

thanks!

• #### 7.  RE: Complicated IF and Text to Number formula

Posted 04-05-2018 00:18
I think we need to change the 'null' to '0' in the variable.

var number NoteTwoToResubThree=If([Relates]=true, ToDays([Resubmittal #3]-[Notification #2]), 0);

• #### 8.  RE: Complicated IF and Text to Number formula

Posted 04-05-2018 00:22
And yes, you might want to wrap all those variables in a Nz().

var number RunTime=Nz(ToNumber([Run Time]));
var number NoteTwoToResubThree=If([Relates]=true, ToDays(Nz([Resubmittal #3]-[Notification #2])), 0);

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

ToDays(\$DueDate-Today())

• #### 9.  RE: Complicated IF and Text to Number formula

Posted 04-05-2018 19:08
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?

• #### 10.  RE: Complicated IF and Text to Number formula

Posted 04-08-2018 06:43
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()

• #### 11.  RE: Complicated IF and Text to Number formula

Posted 04-10-2018 20:35
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])

• #### 12.  RE: Complicated IF and Text to Number formula

Posted 04-13-2018 14:25
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 "".

• #### 13.  RE: Complicated IF and Text to Number formula

Posted 04-13-2018 14:28
Or it might be a "0"

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