Discussions

Expand all | Collapse all

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

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

    Posted 07-05-2018 15:06
    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.


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

    Posted 07-05-2018 15:08
    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.


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

    Posted 07-05-2018 18:31
    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?


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

     
    Posted 03-05-2020 14:09
    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
    ------------------------------



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

    Posted 03-05-2020 14:54
    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
    ------------------------------