How do you subtract two time values in military format in Google Apps Scripts for a spreadsheet?

Question

One column is labeled Checkin Time and the other is labeled Checkout Time and finally I have a column that is labeled Total Time Worked. The cells in the Total Time worked column should subtract the time in the checkout time column from the time in the Checkin Time column. How do I do this in the script? The times in the Checkin and Checkout cells are in the military time format which I got from the timestamp upon the form submission.

Answer 1

Here's a script that should do what you need:

var MilitaryTime = function(value) {
  this.timeValue = parseInt(value, 10);
  this.hour = Math.floor(this.timeValue / 100);
  this.minute = this.timeValue % 100;
  this.totalMinutes = this.hour * 60 + this.minute;
}
MilitaryTime.fromMinutes = function(totalMinutes){
  var hours = Math.floor(totalMinutes / 60);
  var minutes = totalMinutes % 60;
  return new MilitaryTime(hours * 100 + minutes);
};
MilitaryTime.prototype.subtract = function(otherTime) {
  return MilitaryTime.fromMinutes(this.totalMinutes - otherTime.totalMinutes);
};

function militaryTimeDiff(startTime, endTime) {
  var diff = new MilitaryTime(endTime).subtract(new MilitaryTime(startTime));
  return diff.totalMinutes;
}

The militaryTimeDiff takes as arguments two time values in military format (HHMM) and returns the total number of minutes between them.

You can use it in your spreadsheet formula like this (replace the cell references with the correct ones):

=militaryTimeDiff(A1, A2)

Note: this only works correctly for values within the same day. If you have, for example start time = 2350 and end time = 0125, the function will not realize that the times represent two different days and will return a negative difference.

Answer 2

With the following piece of code you can subtract military formatted times.

Code

/**
 * Subtract Military Formatted Times
 *
 * @param {cell} checkin The time at which you have checked in
 * @param {cell} checkout The time at which you have checked out
 * @return The time difference in Military Time Format
 * @customfunction
 */
function myTime(start, end) {
  var diff = getMs(end) - getMs(start);
  return Utilities.formatDate(new Date(diff), "GMT", "HHmm");
}

function getMs(value) {
  var h, m, vSub, vLen, v = value.toString(); 
  vLen = v.length + 1, vSub = Math.round(vLen / 2, 0) - 1;
  h = v.substring(0, vSub), m = v.substring(vSub);
  return (h * 60 * 60 * 1000) + (m * 60 * 1000);
}

Screenshot

Explained

The getMs() function breaks down the string into two pieces, dependent upon the length of the string; hours and minutes. Returned are the amount of milliseconds.

The myTime() function will calculate the difference between the checkout and the checkin time and return a military time format.

Example

I've created an example file for you: Subtract Military Times
Add the script under Tools > Script editor and press the save button.