Calculate Time Difference in Business Hours

jfischer91

I'm trying to track support ticket completion times. We have 3 date fields: 

creation_date: Date a ticket is submitted by a client

approved_date: Date the ticket is assigned to a technician

completion_date: Date the support ticket is resolved.

We want to calculate the total ticket time, basically completion_date - creation_date. But, often tickets are submitted on a Friday and not resolved until Monday, and using DateDiff() will skew the times drastically. Similar situations occur overnight or outside of business hours. I'm less worried about random Monday holidays and such.

Has anyone written an Arcade expression to calculate this? I'm testing some but not making much progress yet. Assuming we need to use Weekday() to get the day of the work week and somehow use that to exclude weekend times? Any thoughts would be much appreciated!

Hayden Welch

Could you just do some mod math on the day to prevent incrementing days on weekends? Something like this:

started_day = WeekDay($feature.started)
days_to_complete = DateDiff($feature.completed, $feature.started, 'days')

excluding_weekends = 0
for (var d=0; d<=days_to_complete; d++) {
    var cur_day = (d+started_day) % 7
    if (cur_day != 6 and cur_day != 0) {
        excluding_weekends += 1
    }
}

 

And to bump late night tickets into the next day:

started_day = WeekDay($feature.started)
started_hour = Hour($feature.started)

if started_hour > 17:
    started_day += 1
    
days_to_complete = DateDiff($feature.completed, $feature.started, 'days')

excluding_weekends = 0
for (var d=0, d<=days_to_complete, d++) {
    var cur_day = (d+started_day) % 7
    if (cur_day != 6 and cur_day != 0) {
        excluding_weekends += 1
    }
}

 

Edit: Sorry, I've been python programming all day and messed up my syntax. Here's a pretty simple expansion of the above examples with an added holiday check:

// Fill this out with days of each month that are holidays
var HOLIDAYS = {
    '1': [1, 20],
    '2': [17],
    '3': [],
    '4': [],
    '5': [26],
    '6': [19],
    '7': [4],
    '8': [],
    '9': [1],
    '10': [13],
    '11': [11, 27],
    '12': [24, 25],
}

function is_weekend(date) {
    return WeekDay(date) == 6 || WeekDay(date) == 0
}

function is_holiday(date) {
    return Includes(HOLIDAYS[Text(Month(date))], Day(date))
}

function is_workday(date) {
    return !is_weekend(date) && !is_holiday(date)
}
var days_to_complete = DateDiff($feature.completed, $feature.started, 'days')
if (Hour($feature.started) > 17) {
    days_to_complete += 1
}

var workdays_to_complete = 0
for (var d=0; d<=days_to_complete; d++) {
    if (is_workday(DateAdd($feature.started, d, 'days'))) {
        workdays_to_complete += 1
    }
}