Business-time calculation

Informer does not yet have a built-in business-time function, and the original Informer 4 businessTimeBetween function does not work in Informer 5. Until a native function ships, use the Calculated Column below, which counts the milliseconds of business time between two dates, excluding weekends and holidays.

Paste it into a Java Calculated Column (an Advanced Flow Step), since it uses the Java date utilities. Replace startAlias and endAlias with your start and end date or datetime Field aliases, adjust workstart and workend (24-hour clock), and list your holidays. The result is in milliseconds, so convert it in a separate Calculated Column or Saved Function as needed. Expect slower performance than a native Field.

var start = startAlias;
var end = endAlias;
var output = 0;

if (!start) start = new java.util.Date();
if (!end) end = new java.util.Date(); // use today

// Set work day parameters with a 24 hour clock
var workstart = 8;  // 8 am start
var workend = 18;   // 6 pm stop

// Set Holidays. Use "mm/dd" for every year, or "mm/dd/yyyy" for a single year.
var holidays = new Array();
holidays.push("1/1");    // New Year's Day
holidays.push("12/25");  // Christmas
// Add year-specific holidays as needed, for example:
// holidays.push("11/28/2024"); // Thanksgiving 2024

// DO NOT EDIT BELOW THIS LINE
// ============================

var startCal = java.util.Calendar.getInstance();
startCal.setTime(start);
var endCal = java.util.Calendar.getInstance();
endCal.setTime(end);

var holCals = new Array(); // holds all holidays as calendars
for (i in holidays) {
  var temp = holidays[i].split("/");
  if (temp[2]) {
    holCals.push(java.util.Calendar.getInstance());
    holCals[holCals.length-1].set(java.util.Calendar.YEAR, parseInt(temp[2]));
    holCals[holCals.length-1].set(java.util.Calendar.MONTH, parseInt(temp[0])-1);
    holCals[holCals.length-1].set(java.util.Calendar.DATE, parseInt(temp[1]));
  } else {
    // add the holiday for the start year, end year, and all years between
    for (j = startCal.get(java.util.Calendar.YEAR); j <= endCal.get(java.util.Calendar.YEAR); j++) {
      holCals.push(java.util.Calendar.getInstance());
      holCals[holCals.length-1].set(java.util.Calendar.YEAR, j);
      holCals[holCals.length-1].set(java.util.Calendar.MONTH, parseInt(temp[0])-1);
      holCals[holCals.length-1].set(java.util.Calendar.DATE, parseInt(temp[1]));
    }
  }
}

var diffMS = 0;
var tempCal = java.util.Calendar.getInstance();

var startOnHoliday = false;
var endOnHoliday = false;
for (var i in holCals) {
  if (startCal.get(java.util.Calendar.DAY_OF_YEAR) == holCals[i].get(java.util.Calendar.DAY_OF_YEAR) && startCal.get(java.util.Calendar.YEAR) == holCals[i].get(java.util.Calendar.YEAR)) {
    startOnHoliday = true;
  }
  if (endCal.get(java.util.Calendar.DAY_OF_YEAR) == holCals[i].get(java.util.Calendar.DAY_OF_YEAR) && endCal.get(java.util.Calendar.YEAR) == holCals[i].get(java.util.Calendar.YEAR)) {
    endOnHoliday = true;
  }
  if (startOnHoliday && endOnHoliday) break;
}

var sameday = false;
var startAfterHours = false;
var endBeforeHours = false;
var startOnWeekend = false;

if (startCal.get(java.util.Calendar.DAY_OF_YEAR) == endCal.get(java.util.Calendar.DAY_OF_YEAR) && startCal.get(java.util.Calendar.YEAR) == endCal.get(java.util.Calendar.YEAR)) {
  sameday = true;

  // handle starting after hours, e.g. 9 p.m.
  tempCal.setTime(start);
  zeroTimeOfDay(tempCal);
  tempCal.set(java.util.Calendar.HOUR_OF_DAY, workend);
  if (startCal.after(tempCal)) startAfterHours = true;

  // handle ending in the morning
  tempCal.set(java.util.Calendar.HOUR_OF_DAY, workstart);
  if (endCal.before(tempCal)) endBeforeHours = true;

  // started and ended on a weekend: 0 business hours
  if (isWeekend(startCal)) startOnWeekend = true;

  // ignore time before the start of business
  if (startCal.get(java.util.Calendar.HOUR_OF_DAY) < workstart) {
    zeroTimeOfDay(startCal);
    startCal.set(java.util.Calendar.HOUR_OF_DAY, workstart);
  }

  // ignore any time after the end of business
  if (endCal.get(java.util.Calendar.HOUR_OF_DAY) >= workend) {
    zeroTimeOfDay(endCal);
    endCal.set(java.util.Calendar.HOUR_OF_DAY, workend);
  }

  output = Math.abs(endCal.getTimeInMillis() - startCal.getTimeInMillis());
}

// Handle a partial start day, if needed
if (!isWeekend(startCal) && !startOnHoliday) {
  if (startCal.get(java.util.Calendar.HOUR_OF_DAY) < workend) {
    if (startCal.get(java.util.Calendar.HOUR_OF_DAY) < workstart) {
      zeroTimeOfDay(startCal);
      startCal.set(java.util.Calendar.HOUR_OF_DAY, workstart);
    }
    tempCal.setTime(start);
    zeroTimeOfDay(tempCal);
    tempCal.set(java.util.Calendar.HOUR_OF_DAY, workend);
    diffMS += tempCal.getTimeInMillis() - startCal.getTimeInMillis();
  }
}

// Handle a partial end day, if needed
if (!isWeekend(endCal) && !endOnHoliday) {
  if (endCal.get(java.util.Calendar.HOUR_OF_DAY) >= workstart) {
    if (endCal.get(java.util.Calendar.HOUR_OF_DAY) >= workend) {
      zeroTimeOfDay(endCal);
      endCal.set(java.util.Calendar.HOUR_OF_DAY, workend);
    }
    tempCal.setTime(end);
    zeroTimeOfDay(tempCal);
    tempCal.set(java.util.Calendar.HOUR_OF_DAY, workstart);
    diffMS += endCal.getTimeInMillis() - tempCal.getTimeInMillis();
  }
}

// Count the full days between start and end, excluding the partial days handled above
zeroTimeOfDay(startCal);
startCal.add(java.util.Calendar.DATE, 1);
zeroTimeOfDay(endCal);
var numFullDays = (endCal.getTimeInMillis() - startCal.getTimeInMillis()) / (1000*60*60*24);

numFullDays -= countWeekendDays(startCal, endCal);

for (i in holCals) {
  if (!isWeekend(holCals[i]) && between(startCal, endCal, holCals[i])) {
    numFullDays--;
  }
}

// convert full days to ms using business hours per day, not 24 hours
diffMS += (numFullDays * 1000*60*60*(workend-workstart));
output = Math.abs(diffMS);

if (sameday && (startOnWeekend || startOnHoliday || startAfterHours || endBeforeHours)) output = 0;

function between(s, e, t) {
  return (s.before(t) && e.after(t));
}

function isWeekend(cal) {
  var calDay = cal.get(java.util.Calendar.DAY_OF_WEEK);
  return (calDay == java.util.Calendar.SUNDAY || calDay == java.util.Calendar.SATURDAY);
}

function zeroTimeOfDay(cal) {
  cal.set(java.util.Calendar.HOUR_OF_DAY, 0);
  cal.set(java.util.Calendar.MINUTE, 0);
  cal.set(java.util.Calendar.SECOND, 0);
  cal.set(java.util.Calendar.MILLISECOND, 0);
}

function countWeekendDays(s, e) {
  var startDay = s.get(java.util.Calendar.DAY_OF_WEEK);
  var endDay = e.get(java.util.Calendar.DAY_OF_WEEK);
  var ndays = Math.floor((e.getTimeInMillis() - s.getTimeInMillis()) / (24*3600*1000));
  var nweeks = Math.floor(ndays/7);
  var nweekendDays = 2*nweeks;

  if ((startDay == java.util.Calendar.SUNDAY || startDay == java.util.Calendar.SATURDAY) && (endDay == java.util.Calendar.SUNDAY || endDay == java.util.Calendar.SATURDAY)) {
    if (startDay != endDay) nweekendDays++;
  } else if (startDay == java.util.Calendar.SATURDAY || startDay == java.util.Calendar.SUNDAY) {
    if (startDay == java.util.Calendar.SATURDAY) nweekendDays += 2;
    else if (startDay == java.util.Calendar.SUNDAY) nweekendDays++;
  } else if (endDay == java.util.Calendar.SATURDAY || endDay == java.util.Calendar.SUNDAY) {
    if (endDay == java.util.Calendar.SUNDAY) nweekendDays++;
  } else if (endDay < startDay) {
    nweekendDays += 2;
  }
  return nweekendDays;
}

output;