Discussions

Expand all | Collapse all

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


  • 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 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


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


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

    Posted 04-04-2018 18:33
    Field [Notification 1 Forecast] formula: 

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


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