Google-sheets – How to subtract two time values in military format in Google Apps Scripts for a spreadsheet

google sheetsgoogle-apps-script

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.

Best Answer

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.

Related Topic