Arcade: Return a date in the future not including weekends and holidays

[Ser_Gio]

Hello, 

I'm trying to use the dateadd function in order to print out a work date in the future and it's working, but returning an incorrect date. Technically it is performing properly, but I am stumped at how to account for holidays and weekends so that I'm only dealing with working dates with Arcade. 

var reference = 2.34;
//var difference = $datapoint["count_ObjectID"]-reference;
//var absoluteDifference = abs(difference);
var ratio = $datapoint["count_ObjectID"]/reference;
//var ratioChange = difference/reference;
//var percentage = ratio*100;
//var percentChange = ratioChange*100
var current = Today()
var Finish = text((DateAdd(current, ratio, 'days')), 'M/D/Y')
var ratioCeil = Ceil(ratio)

return {
    //textColor:'',
    //backgroundColor:'',
    topText: ratioceil + '* ',
    topTextColor: '#e60000',
    //topTextOutlineColor: '',
    topTextMaxSize: 'x-large',
    middleText: Finish,
    middleTextColor: '#e60000',
    middleTextOutlineColor: '',
    middleTextMaxSize: 'x-large',
    //bottomText: '',
    //bottomTextColor: '',
    //bottomTextOutlineColor: '',
    //bottomTextMaxSize: 'medium',
    //iconName:'',
    //iconAlign:'left',
    //iconColor:'',
    //iconOutlineColor:'',
    //noValue:false,
    //attributes: {
      // attribute1: '',
      // attribute2: ''
    // }
  }

The 2/23/2022 date should be 03/16/2022 if it took weekends and holidays into account as days not to be counted against the addition to the current date. 

In the image below you can see the table I am referencing for some other widgets in the same dashboard to see that weekends and holidays are not included when I use this table. 

I'm not sure how I could exclude the few holidays and all weekends in the DateAdd function count.

[DougGreen]

That is perplexing. I think the weekend could be solved by checking the weekday of the forecasted date and if weekday is 6 or 7, add 1 or 2 days respectively.

About the holiday question, that's a bit harder to tackle. There are a variety of Holiday calendars. This post has a known list and then checks against that list to add days if needed. You could create a hosted layer that contains all of the holidays for your locality and add the dates for a year at a time before the first day of the year and then use it in a FeatureSet to check for Holidays.

[jcarlson]

Like @DougGreen  said, holidays can be trickier, but I think we can come up with a decent expression for the weekends. Based on your code, it looks like the ratio variable is equivalent to the "number of workdays", and we're taking the ceiling of partial days.

In short:

1. Figure out how many work weeks are represented by the "days" count.
2. Figure out how many weekends fall in that span.
3. Adjust the output accordingly.

The code:

var reference = 2.34

var daysToFinish = $datapoint["count_ObjectID"]/reference;

var current = Today()

// Calculate number of "work weeks" (5 days) covered by number
var workWeeks = Floor(daysToFinish/5)

// Calculate number of reamining days after the work weeks have elapsed
var remainderDays = Ceil(daysToFinish % 5)

// Get current weekday number
var currentWorkDay = Weekday(current)

// If current weekday plus remainder days crosses a weekend, add two more days
if (currentWorkDay + remainderDays > 5) {
    remainderDays += 2
}

var endDate = DateAdd(current, (workWeeks * 7) + remainderDays, 'days')

return endDate

As a proof of concept, I hard-coded 56 into the expression to test it.

So, there's your weekends!

Holidays, though, I think there's no way to do it without a list of holidays to check against. But supposing you did have such a list, it would look like this:

var holidays = [
    Date(2022, 1, 1), // New Year's Day
    Date(2022, 1, 17), // MLK Day
    ... //etc
]

// Check if each holiday falls within the start and end dates and increment the remainder days as needed
for (var h in holidays){
    if (h >= current && h <=  endDate){
        remainderDays += 1
    }
}

The trouble with this, though, is if there are added holidays, the endDate changes, and you'll need to re-check if it lands in a weekend and move the date accordingly.

I think you might need to wrap some of this into a custom function to make it easier to re-calculate as the end date moves.

[Ser_Gio · Accepted solution]

Hi,

So I have two solutions that a friend and I (mostly him) were able to come up with, but while testing it we realized that while the test environment seemed to be executed properly, the dashboard indicator said "Unable to execute Arcade script". We performed some process of elimination and discovered that our custom functions seemed to be working in the test environment, but failing in the display widget for the dashboard. Below is the code that failed. After that snippet is the final code rewritten to not use functions (and it displayed properly). The two codes main purpose is to get that date in the future accounting for weekends and holidays, but the second code is modified to show other relevant data.

Does anyone know why the first code, which is cleaner and passed the environment test failed to display in the widget?

var reference = 2.34
var daystofinish = $datapoint["count_ObjectID"]/reference;
function addworkdays (startdate , num_of_working_days) {
    var date = startdate;
    for (var i=0; i < num_of_working_days; i++) {
    date = dateadd(date,1,'days');
    date = avoidweekends(date);
    }
    return date;
}
function avoidweekends (funcdate) {
    var holidays = 
[   Date(2022, 1, 2), // New Year's Day
    Date(2022, 12, 31), // New Year's Day
    Date(2022, 0, 17), // MLK Day
    Date(2022, 0, 18), // Susan B Anthony Day
    Date(2022, 0, 19), // Memorial Day
    Date(2022, 5, 20), // Juneteeth Day
    Date(2022, 7, 4), // Independence Day
    Date(2022, 9, 5), // Labor Day
    Date(2022, 10, 10), // Columbus Day
    Date(2022, 11, 11), // Veteran's Day
    Date(2022, 11, 24),  // Thanksgiving Day
    Date(2022, 12, 26), // Christmas Day
]
    var sunday = 0;
    var saturday = 6;
    if (weekday(funcdate) == sunday || weekday(funcdate) ==saturday || Includes(holidays, funcdate)) {
    funcdate = dateadd(funcdate, 1,'days');
    return avoidweekends(funcdate);
    }else{
        return funcdate;
    }
}

var outdate = addworkdays(today(), (daystofinish));

return {
middleText: outdate
}

//this code passed the test environment but failed in the widget as mentioned above. 

Once the functions were eliminated,  accounting for predictable holidays was done with some messier coding, but it appears to be working properly. See below for the solution that worked in the widget.

var holidays = 
[   Date(2021, 11, 31), // New Year's Day
    Date(2022, 0, 17), // MLK Day
    Date(2022, 1, 21), // presday
    Date(2022, 4, 30), // Memorial Day
    Date(2022, 5, 20), // Juneteeth Day
    Date(2022, 6, 4), // Independence Day
    Date(2022, 8, 5), // Labor Day
    Date(2022, 9, 10), // Columbus Day
    Date(2022, 10, 11), // Veteran's Day
    Date(2022, 10, 24),  // Thanksgiving Day
    Date(2022, 10, 25),  // day after Thanksgiving Day
    Date(2022, 11, 26), // Christmas Day
]

var sunday = 0;
var saturday = 6;
var currentday = today();
var sitesleft = $datapoint["count_ObjectID"]
var rate = 2.34
var days_to_add = ceil(sitesleft/rate);
var calculated_date = currentday;
for (var i=0; i < days_to_add; i++) {
    calculated_date = dateadd(calculated_date,1,'days');
    if (weekday(calculated_date) == saturday){ 
        calculated_date = dateadd(calculated_date, 2,'days')
    }
    if (weekday(calculated_date) == sunday){ 
        calculated_date = dateadd(calculated_date, 1,'days');
    }
    // at this point the calcdate should 
    //be a weekday but could still be a holiday
    if (Includes(holidays, calculated_date)){ 
        // checks if this new date is a holiday
        if (weekday(calculated_date) == 4){ 
            //checks if its a thursday holiday
            if (Includes(holidays,dateadd(calculated_date,1,'days'))){
                // followed by a fri holiday aka thanksgiving
            calculated_date = dateadd(calculated_date, 4,'days');
            //adds 4 days to thursday to get to monday
            }else{
                /* if its not thanksgiving then Fri is not a holiday, 
                so add 1 day to get to Fri*/
                calculated_date = dateadd(calculated_date, 1,'days');
            } 
        }
        if (weekday(calculated_date) > 0 && 
            weekday(calculated_date) < 4){
            // if the holiday lands on a mon,tues,weds
            calculated_date = dateadd(calculated_date, 1,'days');
        }
        if (weekday(calculated_date) == 5){
            // if the holiday lands on a Fri
            calculated_date = dateadd(calculated_date, 3,'days');
        }
    }
}
return {
    topText: sitesleft+ ' inspections reports remaining* ',
    topTextMaxSize: 'medium',
    middleText: days_to_add+ '** inspection days remaining',
    middleTextMaxSize: 'medium',
    bottomText: text((calculated_date), 'M/D/Y')+ ' end date',
    bottomTextMaxSize: 'Large',
    bottomTextColor: IIf(calculated_date >= Date(2022,03,28), 'red', 
    'green')
    
}

[Ser_Gio]

Thank you guys! Testing it now and will let you know! 

[Ser_Gio]

The top half of your response @jcarlson worked well. Thank you!

I tried to implement the holiday portion but it isn't working. It's not failing or calling out any exceptions, but it doesn't seem to add any days. I noticed also that (2022, 1,1) is (year, month (0:Jan, 1:Feb, etc), day)), but it didn't help when I updated it. I keep having MLK day counted as a workday despite having put it as a holiday. 

[Ser_Gio]

My reply was labeled as spam by accident i think. I believe a friend and I found two solutions, one which won't post to the indicator dashboard and one which does. The only main difference we can note being the use of functions.

Why would the code below return as ok in the test environment but unable in the dashboard?

function addworkdays (startdate , num_of_working_days) {
    var date = startdate;
    for (var i=0; i < num_of_working_days; i++) {
    date = dateadd(date,1,'days');
    date = avoidweekends(date);
    }
    return date;
}
function avoidweekends (funcdate) {
    var holidays = 
[   Date(2022, 1, 2), // New Year's Day
    Date(2022, 12, 31), // New Year's Day
    Date(2022, 0, 17), // MLK Day
    Date(2022, 0, 18), // Susan B Anthony Day
    Date(2022, 0, 19), // Memorial Day
    Date(2022, 5, 20), // Juneteeth Day
    Date(2022, 7, 4), // Independence Day
    Date(2022, 9, 5), // Labor Day
    Date(2022, 10, 10), // Columbus Day
    Date(2022, 11, 11), // Veteran's Day
    Date(2022, 11, 24),  // Thanksgiving Day
    Date(2022, 12, 26), // Christmas Day
]
    var sunday = 0;
    var saturday = 6;
    if (weekday(funcdate) == sunday ||
        weekday(funcdate) ==saturday ||
        Includes(holidays, funcdate)) {
    funcdate = dateadd(funcdate, 1,'days');
    return avoidweekends(funcdate);
    }else{
        return funcdate;
    }
}
var outdate = addworkdays(today(), 1);
return {
  middleText: outdate
}