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
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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

 

 

Photo of Erika D

Erika D

  • 0 Points

Posted 4 years ago

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

IF (IsNull([my field]),"Please check your dates")






Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
>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!

https://www.youtube.com/watch?v=WANNqr-vcx0
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
Not tested but try this:

UPDATED MULTIPLE TIMES: http://pastie.org/pastes/10282332/text
Photo of Erika D

Erika D

  • 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]))
Photo of Erika D

Erika D

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