Forum Discussion

AmyGosz2's avatar
AmyGosz2
Qrew Cadet
7 years ago

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

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.
  • 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.
  • 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?
    • UrsulaLlaveria's avatar
      UrsulaLlaveria
      Qrew Assistant Captain
      Amy, were you able to figure this out? We are coming across a similar issue, trying to calculate a due date to include only workdays and exclude Federal Holidays. We have figured out the workdays, but still haven't come up with an easy solution for holidays that isn't user friendly.

      ------------------------------
      Ursula
      ------------------------------
      • BrandonDrake's avatar
        BrandonDrake
        Qrew Cadet
        Hello Ursula, 

        If you are already taking the vacations into account, which it seems you are, you could wrap the entire result in ToWeekdayP. 

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

        This would ensure that whatever day you calculate from your current formula will go to the previous weekday if it lands on a weekend... 


        ------------------------------
        Brandon Drake
        JHI Group
        Monroeville OH
        ------------------------------