Discussions

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. 

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

Field [Expiration Date] formula: Basically what I called 'DueDate' above.

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);


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

Field [Notification 1 Forecast] formula: 

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

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

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.

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!

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);

And yes, you might want to wrap all those variables in a Nz().

var number RunTime=Nz(ToNumber([Run Time]));
var number NoteOneToResubTwo=ToDays(Nz([Resubmittal #2]-[Notification #1]));
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())

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?

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()

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])

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 "".

Or it might be a "0"

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