Complicated IF and Text to Number formula
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: ...
Forum Discussion
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
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 "".
