I want to find the Cancellation Deadline XX busines days prior to Start Date

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I want to find the Cancellation Deadline as a Date using a Formula Date field.


I have a [Start Date] field and 

[Cancellation Notice] field of type Duration but I can change it to Numeric or text.


IF a class Starts on  11-6-2015 and the class has a 10 day Cancellation Notice prior to that Start Date, what would that formula look like.

Photo of Nathan

Nathan

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
I suggest making the cancellation notice field be a numeric field.

Then the formula for [Cancelation Date] would be

[Start Date] - Days([Cancellation Notice])
Photo of Nathan

Nathan

  • 0 Points
Since my policy is "business days" or essentially the work week, can i somehow utillize the workdays function to account for the weekends?
Photo of Nathan

Nathan

  • 0 Points
working on this further:
WeekdayAdd([Start Date], -[Cancellation Notice])  validates but doesnt compute correctly.  

Only when I do this:
WeekdayAdd([Start Date], -10)     does it compute correctly

Obviously, I want it to pull from the field.which is numeric....

Thanks for your help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
WeekdayAdd([Start Date], -[Cancellation Notice])   should work correctly if you have set the [Cancellation days] to be type numeric.  When you say it does not compute correctly, what do you mean by that?  Are you saying. a syntax error or the math calculation is not correct.
Photo of Nathan

Nathan

  • 0 Points
[Cancellation Notice] is a numeric Lookup Field from an other table.  Does that matter?   Right now it just returns a date that is not calculated correctly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
For an example record, can you tell me the Start Date, the Cancellation Notice, the formula result and the answer you were wanting?
Photo of Nathan

Nathan

  • 0 Points
Strangely it is now working.  Thanks for your help.  Your questions back to me helped me think this through better.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
Great to hear.