I am trying to build a formula to solve for duration of time and multiple f statements. Also need the duration result to be in weekdays.i

• 0
• Question
• Updated 4 years ago

If [Hold Begin Date] is null

1)TRUE

If [Offer Completion Date] is null

a)TRUE (Today()-[COE Lead Start Date])

b)FALSE ([Offer Completion Date]-[COE Lead Start Date])

2)FALSE

If [Hold End Date] is null

a)TRUE

If [Offer Completion Date] is null

i)TRUE ([COE Lead Start Date]-[Hold Begin Date])

ii)FALSE Some type of message to require hold end date

b)FALSE

If [Offer Completion Date] is null

i)TRUE (Today()-[COE Lead Start Date]) - ([Hold Begin Date]-[Hold End Date])

ii)FALSE ([Offer Completion Date]-[COE Lead Start Date]) - ([Hold Begin Date]-[Hold End Date])

• 0 Points

Posted 4 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 67,760 Points
imho, the best way to do these complicated ones is brute force and eliminate the nesting because its too complicated to get the nesting of the conditions correct..

I have not done our whole formula, but here is a start.

IF(

isnull([Hold Begin Date]) and isnull([Offer Completion Date]), WeekDaySub(Today(),[COE Lead Start Date]),

isnull([Hold Begin Date]) and not isnull([Offer Completion Date]), WeekDaySub([Offer Completion Date], [COE Lead Start Date]),

not isnull([Hold Begin Date]) and isnull([Hold End Date]) and  isnull([Offer Completion Date]), WeekdaySub([COE Lead Start Date], [Hold Begin Date])

....

note also that at some point you want to put up a message as a result.  This will be a formula numeric field, so you cannot have a text result.  So, just do not calculate a value for that situation and it will result in null.  Then have a formula text field check this field for IsNull (   ) and have it put out a message.

like

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
>imho, the best way to do these complicated ones

I would use Lewis Carroll's Trilateral Diagram method to solve this type of problem. Basically you have three variables you are testing for null and considering all possible combinations of the three variables:

Introducing some shorthand notation:

IsNull( [Hold Begin Date]) => IsNull([HBD]) => x
IsNull([Hold End Date]) => IsNull([HED]) => y
IsNull([Offer Completion Date]) => IsNull([OCD]) => z

Lewis Carroll's Trilateral Diagrams
http://www.cut-the-knot.org/LewisCarroll/tridiagram.shtml

Just remove the eliminands and keep the retinends.

White Rabbit agrees with me ... feed your head!

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,074 Points
Not tested but try this:

UPDATED MULTIPLE TIMES: http://pastie.org/pastes/10282332/text
• 0 Points
Thank you for your help. When I try to repeat methodology for the rest of the formula I am still getting syntax errors...Do you know why?
IF(
isnull([Hold Begin Date]) and isnull([Offer Completion Date]), WeekDaySub(Today(),[COE Lead Start Date]),
isnull([Hold Begin Date]) and not isnull([Offer Completion Date]), WeekDaySub([Offer Completion Date], [COE Lead Start Date]),
not isnull([Hold Begin Date]) and isnull([Hold End Date]) and isnull([Offer Completion Date]), WeekdaySub([COE Lead Start Date], [Hold Begin Date]),
not isnull([Hold Begin Date]) and not isnull([Hold End Date]) and isnull([Offer Completion Date]), WeekdaySub(Today()-[COE Lead Start Date], [Hold Begin Date]-[Hold End Date]),
not isnull([Hold Begin Date]) and not isnull([Hold End Date]) and not isnull([Offer Completion Date]), WeekdaySub([Offer Completion Date]-[COE Lead Start Date], [Hold Begin Date]-[Hold End Date]))
• 0 Points
I figured out the rest. :) Now just the message part.

If(

IsNull([Related Time Slot2 - Hold Begin Date]) and IsNull([Offer Completion Date]), WeekdaySub(Today(),[COE Lead Start Date]),

IsNull([Related Time Slot2 - Hold Begin Date]) and not IsNull([Offer Completion Date]), WeekdaySub([Offer Completion Date], [COE Lead Start Date]),

not IsNull([Related Time Slot2 - Hold Begin Date]) and IsNull([Related Time Slot2 - Hold End Date]) and  IsNull([Offer Completion Date]), WeekdaySub([Related Time Slot2 - Hold Begin Date], [COE Lead Start Date]),

not IsNull([Related Time Slot2 - Hold Begin Date]) and not IsNull([Related Time Slot2 - Hold End Date]) and  IsNull([Offer Completion Date]), (WeekdaySub(Today(),[COE Lead Start Date]) - WeekdaySub([Related Time Slot2 - Hold End Date], [Related Time Slot2 - Hold Begin Date])),

not IsNull([Related Time Slot2 - Hold Begin Date]) and not IsNull([Related Time Slot2 - Hold End Date]) and not IsNull([Offer Completion Date]), (WeekdaySub([Offer Completion Date],[COE Lead Start Date]) - WeekdaySub([Related Time Slot2 - Hold End Date], [Related Time Slot2 - Hold Begin Date])))