I need to calculate a due date based on the month end date but excluding weekends and holidays.

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
I need to calculate a due date based on the month end date but exclude weekends/holidays. The actual due date could be before or after the month end date.

For example, month end is 6-30-18. A task due -15 days, would have a due date of 6-11-2018. A task due +3 days, would have a due date of 7-5-2018. I'm sure I'm just missing setting up one field as a go-between, but just am not sure what to do.
Photo of Amy

Amy

  • 50 Points

Posted 4 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,046 Points 50k badge 2x thumb
Try wrapping your result with WeekDayAdd([my date field],0)
That will result in a non weekend date but I forget if it moves it to the Friday or the Monday.
Photo of Amy

Amy

  • 50 Points
I have tried that but have the Holidays exclusion included. I have a formula field called "Date Due 2018" with the formula:

var number DaysToFollowUp = ToNumber(Left([Task - Day Due], " "));
var number Holidays = ToNumber(Left([Holiday Allowance Duration], " "));

WeekdayAdd([First Day of Next Month], ($DaysToFollowUp + $Holidays))

this worked fine for the negative Day Due records and the records 3+ days due. However, for the Day Due 1 or 2, it calculated July 3 and July 4, which should be July 2 and July 3. Day Due for 3 calculated corrected as July 5 and all are ok beyond that. Why would Day Due 1 and 2 not calculate correctly?