Google Spreadsheet script for time functions

time-functions.js

/**
 * @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
 */

/**
 * Round time to the nearest interval.
 *
 * Conversion of 
 * TIME(HOUR(time), (FLOOR(MINUTE(time)/15)*15/60) * 60, 0)
 *
 * @param {Date} value The time value to round.
 * @param {Boolean} direction_down The direction to round.
 * @param {Number} interval The interval to round by.
 *
 * @return {Date} The rounded time.
 * @customfunction
 */
function ROUND_TIME_INTERVAL(value, direction_down, interval) {
  
//  if (value.map) {
    
//    return value.map(ROUND_TIME_INTERVAL, direction_down, interval);
//  }

  var interval = interval * 60;
  var date = new Date(value);

  // Convert into number of 15 minute intervals.
  var hour_portion = date.getMinutes() / interval;
  
  // Round as appropriate.
  if (direction_down) {
    
    hour_portion = Math.floor(hour_portion);
  }
  else {

    hour_portion = Math.ceil(hour_portion);
  }

  // Convert back to minutes
  hour_portion = hour_portion * interval / 60 * 60;

  date.setMinutes(hour_portion);

  return date;
}

/**
 * Round time up to the nearest interval.
 *
 * @param {Date} value The time value to round.
 * @param {Number=} interval The interval to round by.
 *
 * @return {Date} The rounded time.
 * @customfunction
 */
function ROUND_TIME_UP(value, interval) {
  
  if (!interval) {
    
    interval = .25;
  }
  
  return ROUND_TIME_INTERVAL(value, 0, interval);
}

/**
 * Round time down to the nearest interval.
 *
 * @param {Date} value The time value to round.
 * @param {Number=} interval The interval to round by.
 *
 * @return {Date} The rounded time.
 * @customfunction
 */
function ROUND_TIME_DOWN(value, interval) {
  
  if (!interval) {
    
    interval = .25;
  }
  
  return ROUND_TIME_INTERVAL(value, 1, interval);
}

/**
 * Get the difference in hours between two times.
 *
 * Conversion of 
 * ROUND(IF(end-start<0,end-start+1,end-start)*24,2)
 *
 * @param {Date} start The start time.
 * @param {Date} end The end time.
 *
 * @return {Number} The difference.
 * @customfunction
 */
function TIME_DIFFERENCE(start, end) {

  // Adjust for end durations that are at midnight.
  var end_hour = end.getHours();
  if (0 == end_hour) {
    
    end_hour = 24;
  }
  
  var hour_diff = end_hour - start.getHours();
  var min_diff = end.getMinutes() - start.getMinutes();

  var diff = (hour_diff * 60 + min_diff) / 60;
  
  return diff.toFixed(2);
}