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