# Complicated IF and Text to Number formula

• 0
• Question
• Updated 1 year ago
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.
• 284 Points
• overwhelmed and confused.

Posted 1 year ago

• 0
• 31,758 Points
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
• 284 Points
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?
• 31,758 Points
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()
• 284 Points
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])
• 31,758 Points
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)
• 31,758 Points
Or it might be a "0"

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